MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Greater than and Less than on Char Column

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
chandra
Beginner


Joined: 26 Sep 2003
Posts: 130
Topics: 36

PostPosted: Mon Apr 28, 2014 2:06 pm    Post subject: Greater than and Less than on Char Column Reply with quote

Hi All,

I have a query in COBOL program as follows.

Code:
SELECT BEG_CODE
      ,END_CODE
  FROM CE01T
 WHERE BEG_CODE       <= :WS-CODE
  AND END_CODE       >= :WS-CODE


The BEG_CODE and END_CODE are Char(12) columns and they can have any alpha numeric values in them including decimal values.

When I am giving my WS-CODE is F558 I am getting BEG_CODE = F20 and END_CODE = F99 which not correct.

I expected values BEG_CODE = F500 and END_CODE = F600

Can you please let me know how to get correct values.

Thank you !!
Back to top
View user's profile Send private message
NASCAR9
Intermediate


Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California

PostPosted: Mon Apr 28, 2014 3:24 pm    Post subject: Reply with quote

Code:

SELECT BEG_CODE
       ,END_CODE
   FROM CE01T
  WHERE BEG_CODE       <= :WS-CODE
   AND END_CODE       >= :WS-CODE


This seems backwards to me.

Should it be
Code:

SELECT BEG_CODE
       ,END_CODE
   FROM CE01T
  WHERE BEG_CODE       >= :WS-CODE
   AND END_CODE         <=   :WS-CODE

_________________
Thanks,
NASCAR9
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12373
Topics: 75
Location: San Jose

PostPosted: Mon Apr 28, 2014 4:43 pm    Post subject: Reply with quote

chandra,

The problem you have is that the columns you want to validate contain both Character and numeric values. You need to left justify the character values and Right justify the numeric values so that you can get the results you want.

The basic idea is to make your BEG_CODE should be translated to look like

Code:

F20   =   F             000000000020
F500  =   F             000000000500


If you have ABC123DEF then you need to handle them also. I will leave that to you to solve. Here is an untested sql to get the desired results for handling character data followed by numeric data.

Code:

SELECT *                                                                 
  FROM TABLE                                                               
WHERE (TRIM(TRANSLATE(BEG_CODE,'          ','0123456789')) <= 'F'         
  AND  RIGHT('000000000000'||                                             
              TRIM(TRANSLATE(BEG_CODE,'                          ',       
                          'ABCDEFGHIJKLMNOPQRSTUVWXYZ')),12)             
   <=  '000000000558')                                                   
  AND (TRIM(TRANSLATE(END_CODE,'          ','0123456789')) >= 'F'         
  AND  RIGHT('000000000000'||                                             
              TRIM(TRANSLATE(END_CODE,'                          ',       
                          'ABCDEFGHIJKLMNOPQRSTUVWXYZ')),12)             
   >=  '000000000558')                                                   
  ;
Back to top
View user's profile Send private message Send e-mail Visit poster's website
chandra
Beginner


Joined: 26 Sep 2003
Posts: 130
Topics: 36

PostPosted: Tue Apr 29, 2014 9:15 am    Post subject: Reply with quote

Thanks for your reply !!

NASCAR9 - I want the rows whose value is in between BEG_CODE and END_CODE

Kolusu - I will check your solution and let you know how it is working.
_________________
Regards,
Chandra
Back to top
View user's profile Send private message
chandra
Beginner


Joined: 26 Sep 2003
Posts: 130
Topics: 36

PostPosted: Tue Apr 29, 2014 9:52 am    Post subject: Reply with quote

Hi Kolusu,

It is not working as expected.

When I gave the value '00000000055.8' I was expected to see the Beg_code = F20 and END_code = F99 but I got no rows.

I try to change your query as follows then it is displaying all the row like F10-F19.6, F20-F99.

Code:
SELECT ICD_BEG_CODE, ICD_END_CODE
  FROM WTESTP.PDTCE01T
WHERE (TRIM(TRANSLATE(ICD_BEG_CODE,'            ','.0123456789')) <= 'F'
  AND  RIGHT('000000000000'||
        TRIM(TRANSLATE(ICD_BEG_CODE,'                          ',
                          'ABCDEFGHIJKLMNOPQRSTUVWXYZ')),12)
   <=  '00000000055.8')
  AND (TRIM(TRANSLATE(ICD_END_CODE,'            ','.0123456789')) >= 'F'
  AND  RIGHT('000000000000'||
              TRIM(TRANSLATE(ICD_END_CODE,'                          ',
                          'ABCDEFGHIJKLMNOPQRSTUVWXYZ')),12)
   >=  '00000000055.8')
  ;

_________________
Regards,
Chandra
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12373
Topics: 75
Location: San Jose

PostPosted: Tue Apr 29, 2014 10:35 am    Post subject: Reply with quote

chandra wrote:
It is not working as expected.

When I gave the value '00000000055.8' I was expected to see the Beg_code = F20 and END_code = F99 but I got no rows.

I try to change your query as follows then it is displaying all the row like F10-F19.6, F20-F99.


Chandra,

Oh now you have decimal points? How am I supposed to know that? Did you mention it in your initial post? You need to Split the integer and decimal parts separately and do the validation.
Back to top
View user's profile Send private message Send e-mail Visit poster's website
chandra
Beginner


Joined: 26 Sep 2003
Posts: 130
Topics: 36

PostPosted: Tue Apr 29, 2014 4:05 pm    Post subject: Reply with quote

Hi Kolusu,


Quote:

The BEG_CODE and END_CODE are Char(12) columns and they can have any alpha numeric values in them including decimal values.


I have mentioned in my original post in the description but forgot to mention in the example.

I have made small change to your original query and now I am getting expected results. I need to change my host variable to hold the decimal values instead of Char values.

Code:
SELECT ICD_BEG_CODE, ICD_END_CODE
  FROM WTESTP.PDTCE01T
WHERE (TRIM(TRANSLATE(ICD_BEG_CODE,'            ','.0123456789')) = 'F'
  AND  DECIMAL(
  TRIM(TRANSLATE(ICD_BEG_CODE,
                  '                          ',
                          'ABCDEFGHIJKLMNOPQRSTUVWXYZ')),12)
   <=   000000000558 )
  AND (TRIM(TRANSLATE(ICD_END_CODE,'            ','.0123456789')) = 'F'
  AND  DECIMAL(
  TRIM(TRANSLATE(ICD_END_CODE,
             '                          ',
                          'ABCDEFGHIJKLMNOPQRSTUVWXYZ')),12)
   >=   000000000558 )
  ;

_________________
Regards,
Chandra
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group