|
View previous topic :: View next topic
|
| Author |
Message |
cdhami
New User
Joined: 24 Jan 2006
Posts: 28
|
|
|
|
Hi all,
I have a unique problem with respect to db2 table query.
There is a column TRS_AMOUNT which is in char x(17) format ( stored from online screen as characters but input format is 9(15)v(2)) e.g if value is 10, the table contains
value as "10,00______" as it is character.
Problem is when i am using my SQL
EXEC SQL
SELECT FROM TBL
WHERE TRS_AMOUNT > "100,00______"
END-EXEC
i m expecting sqlcode100, but I am getting record bcos
"10,00_____" > "100,00____" in character comparision.
Question:
How do i resolve this as query is not working fine.
1. Is there any way in which i can right justify the db2 column and then do comparision with right justified character variable??
2. Any other way possible?
the Amt column is required to be in character format as it may contain non numeric data also (wild cards *).
Let me know
thanks in advance
cdhami |
|
| Back to top |
|
 |
Raghu navaikulam
Active User
Joined: 27 Sep 2008
Posts: 193
Location: chennai
|
|
|
|
Hi cdhami
| Quote: |
There is a column TRS_AMOUNT which is in char x(17) format ( stored from online screen as characters but input format is 9(15)v(2)) e.g if value is 10, the table contains
value as "10,00______" as it is character. |
If you are getting the data from CICS screen, then you can use the built-in function BIF DEEDIT to remove non-numeric characters from the input.
Eg. You entered ******12345678.90 on the screen. After BIF DEEDIT the data became 1234567890. The last two position can be considered as decimal positions(based on the field you used).
The syntax : EXEC CICS BIF DEEDIT FIELD(data-area) EN-EXEC.
Regards
Raghu |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
the problem is not with Your sql, rather with the design !
why in &heaven a field supposedly a numeric value is defined as a char ?
it would be better to fix the design rather than tweak the sql |
|
| Back to top |
|
|
cdhami
New User
Joined: 24 Jan 2006
Posts: 28
|
|
|
|
Thanks for reply.
the reason the field is kept as character is because the amount field is a parameter which can be kept as wild character also. e.g. '*' is also permitted value to be stored.
This has caused the data definition to be kept as characters.
This should answer raghu's question also.
kindly let me know if any way is present.
Thanks
Cdhami |
|
| Back to top |
|
|
Raghu navaikulam
Active User
Joined: 27 Sep 2008
Posts: 193
Location: chennai
|
|
|
|
Hi Cdhami
Where you data is stored? in DB2 Tables? or Files?
DB2 will not store any wild char in a numeric data type.
You should clarify this, then only a solution can be obtained!
Regards
Raghu |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
since the ts posted in the DB2 forum and
There is a column TRS_AMOUNT which is in char x(17)
I guess that' s all db2 related
this is just a sample of poor everything, design, no prototyping no due diligence in approaching the whole project
and ...
| Code: |
| WHERE TRS_AMOUNT > "100,00______" |
given the TS example the field is supposedly left aligned,
for a right aligned mischief the query should really look like
WHERE TRS_AMOUNT > "bbbbbbbbbbb100,00"
the only thing at this point is to confirm the bad design and a strong suggestion to review the whole thing |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
DB2 will not store any wild char in a numeric data type.
You should clarify this, then only a solution can be obtained! |
It is important to read the topic completely . . .
| Quote: |
the reason the field is kept as character is because the amount field is a parameter which can be kept as wild character also. e.g. '*' is also permitted value to be stored.
This has caused the data definition to be kept as characters.
This should answer raghu's question also. |
explains why the definition is character. . .
As suggested, this is not a good design. How are rules defined for which characters should compare higher or lower than which numeric values. I've not before seen an attempt to use wildcards in the middle of an actual number. . .
If only the numeric value is to be compared (i.e. 1000 versus 10000 with no "wildcard"ing), a separate numeric field might be defined in the table and comparisons done against this.
No matter how this is implemented, i believe there will be problems/issues. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
first the design :
it could be solved a nullable numeric column where null correspond with '*'
secondly as it is now :
| Code: |
with tab1 as (
select '100,00 ' as col1 from sysibm.sysdummy1
union all
select '10,00 ' as col1 from sysibm.sysdummy1
union all
select '1,00 ' as col1 from sysibm.sysdummy1
union all
select '10100,01 ' as col1 from sysibm.sysdummy1
union all
select '* ' as col1 from sysibm.sysdummy1
)
select case when strip(col1) = '*' then -1.00 else dec(strip(col1)) end from tab1 |
|
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
or to compare :
| Code: |
where col1 <> '* '
and dec(strip(col1)) > 100.00 |
|
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
oops, actually DEC() doesn't interpret comma very well it just stops at it.
If you need the part after comma maximum 2,you'll need something like this :
| Code: |
dec(substr(strip(col1),1,posstr (strip(col1),',') - 1),15,2)
+ dec(substr(strip(col1),posstr(strip(col1),',') + 1,2),15,2 ) / 100 |
if the part after comma is variable in length (in the example max 5)
you'll need this :
| Code: |
dec(substr(strip(col1),1,posstr (strip(col1),',') - 1),15,5)
+ dec(substr(strip(col1),posstr(strip(col1),',') + 1),15,5 )
/ power(10,length(substr(strip(col1),posstr(strip(col1),',') + 1))) |
|
|
| Back to top |
|
|
|
|
