Joined: 26 Nov 2002 Posts: 12376 Topics: 75 Location: San Jose
Posted: Mon Apr 28, 2014 4:43 pm Post subject:
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')
;
Joined: 26 Nov 2002 Posts: 12376 Topics: 75 Location: San Jose
Posted: Tue Apr 29, 2014 10:35 am Post subject:
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.
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 )
;
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
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
