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
SELECT FROM TBL
WHERE TRS_AMOUNT > "100,00______"
i m expecting sqlcode100, but I am getting record bcos
"10,00_____" > "100,00____" in character comparision.
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 *).
Joined: 23 Nov 2006 Posts: 19270 Location: Inside the Matrix
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 . . .
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.
first the design :
it could be solved a nullable numeric column where null correspond with '*'
secondly as it is now :
with tab1 as (
select '100,00 ' as col1 from sysibm.sysdummy1
select '10,00 ' as col1 from sysibm.sysdummy1
select '1,00 ' as col1 from sysibm.sysdummy1
select '10100,01 ' as col1 from sysibm.sysdummy1
select '* ' as col1 from sysibm.sysdummy1
select case when strip(col1) = '*' then -1.00 else dec(strip(col1)) end from tab1