strange select results

mlybarger · #1 (**permalink**) 03-04-11, 16:03

I can't quite figure out why this returns FOO
SELECT 'FOO' FROM sysibm.sysdummy1 where '1000' < '11000'

whereas, this does not:
SELECT 'FOO' FROM sysibm.sysdummy1 where '5000' < '11000'

for my problem, i'm comparing two address fields, low_address and high_address, they're numeric fields because they can contain alpha characters. so when i say to db2,

where
'5000' between lowaddress and highaddress

with lowaddresss of 4000 and a high address of 10223, i'm not getting my expected result. help?

tonkuma · #2 (**permalink**) 03-04-11, 16:10

Quote:

I can't quite figure out why this returns FOO
SELECT 'FOO' FROM sysibm.sysdummy1 where '1000' < '11000'

whereas, this does not:
SELECT 'FOO' FROM sysibm.sysdummy1 where '5000' < '11000'

Because, they are comparing strings,
then '1000' < '11000' is Yes and '5000' < '11000' is No.

If you want to compare them as numeric,
then INT('1000') < INT('11000') will be No and INT('5000') < INT('11000') will be Yes.

ARWinner · #3 (**permalink**) 03-04-11, 16:10

You are comparing strings not numbers. As a string '5000' is still > '11000'.

Andy

mlybarger · #4 (**permalink**) 03-04-11, 16:29

ok, i hear what i'm reading, but it just doesn't seem logical to me that comparing strings would be that different than comparing numbers. i'm not comparing numbers, but to me...

'55STR' < '10STRING' < '22STRING'

but to db2,

'5' > '16'

how can i get it so that

'5' < '16'

and

'4' < '16'

dav1mo · #5 (**permalink**) 03-04-11, 16:48

don't make it a string

Code:

SELECT 'FOO' FROM sysibm.sysdummy1 where 5000 < 11000

Strings and numbers are completely different datatypes and sorting is different. Look at those strings as hex data and maybe it will be a bit clearer to you. I've run across this in quite a few places where they think storing a number as CHAR(09) is a good idea, until it comes time to sort by that 'number' or compare by that 'number' as it is not a number. I can't verify it at the moment, but I think it is the same on other DBMS's as well, not just a DB2 thing.
Dave

tonkuma · #6 (**permalink**) 03-04-11, 20:17

Quote:

..... i'm not comparing numbers, but to me...

'55STR' < '10STRING' < '22STRING'

but to db2,

'5' > '16'

Still, your requirements are not clear to me.

If you want to get the result '5' > '16',
it's OK. I could understand that you want to compare as number.

But, how you judge the following comparison?
'55STR' < '10STRING'

Do you want to compare like '5' < '10'?
Why not take '55' from first string for comparison?

r937 · #7 (**permalink**) 03-04-11, 22:18

Quote:

Originally Posted by dav1mo

I can't verify it at the moment, but I think it is the same on other DBMS's as well

yup, in all the ones i've worked with

many will give you a syntax error if, for example, you compare a numeric column with a string...

Code:

WHERE order_qty > '2'

mysql will accept this, and do a "silent" conversion of the string '2' to the numeric value 2

however, mysql will interpret '5' < '16' as false, as expected

tonkuma · #8 (**permalink**) 03-04-11, 22:48

Quote:

'55STR' < '10STRING' < '22STRING'

I realized a way to make this result.
(Though, there must be many other ways...)

The way is to right justify the strings before comparison, like ...

Code:

------------------------------ Commands Entered ------------------------------
WITH sample_data(c1) AS (
VALUES '55STR' , '10STRING' , '22STRING'
)
SELECT a.c1 AS a 
     , b.c1 AS b
     , CASE
       WHEN LPAD(a.c1 , 9) > LPAD(b.c1 , 9) THEN
            'A > B'
       WHEN LPAD(a.c1 , 9) < LPAD(b.c1 , 9) THEN
            'A < B'
       WHEN LPAD(a.c1 , 9) = LPAD(b.c1 , 9) THEN
            'A = B'
       ELSE 'Unknown'
       END  AS comp_lpad_strings
     , CASE
       WHEN a.c1 > b.c1 THEN
            'A > B'
       WHEN a.c1 < b.c1 THEN
            'A < B'
       WHEN a.c1 = b.c1 THEN
            'A = B'
       ELSE 'Unknown'
       END  AS comp_bare_strings
 FROM  sample_data a
 INNER JOIN
       sample_data b
   ON  b.c1 > a.c1
;
------------------------------------------------------------------------------

A        B        COMP_LPAD_STRINGS COMP_BARE_STRINGS
-------- -------- ----------------- -----------------
10STRING 55STR    A > B             A < B            
22STRING 55STR    A > B             A < B            
10STRING 22STRING A < B             A < B            

  3 record(s) selected.

stolze · #9 (**permalink**) 03-07-11, 03:50

You may want to have a look at character sets and how string/byte comparisons work in general. If you look at a string like '1000', you have 4 characters: '1', '0', '0', '0'. If you compare that with a string like '11000', the comparison will test each byte in sequence, starting with the first byte/character: '1' = '1' is true, so the second byte counts. For the second byte, you have the condition '0' < '1' as true, so the first string '1000' is sorted before the other one with '11000'. That's the only logical way to do string comparisons.

If you want to compare by numbers, you have to parse the string, i.e. convert the value represented by a string to a number. SQL is a strongly typed programming language (as opposed to others like Perl), so not a lot of implicit type conversions will take place. In your case, the compiler has no other chance anyway - there are 2 strings, so a regular string comparison is done.

p.s: Those are basics of programming and not related to SQL in general and DB2's dialect specifically.

mlybarger · #10 (**permalink**) 03-07-11, 09:03

thanks for all the help thus far.

the lpad idea seems like what i'm after, but i'm getting a sqlcode-440 trying to run that code. i have udb 9.5 on win xp (develoment), and db2 8 on z/os (test/prod).

actually, the db2 on z/os gives a -104, it doesn't seem to like the with statement, but i'll focus on getting lpad to work.

my simple query:
select lpad('FOO',10) from sysibm.sysdummy1;

gives the same -440.

tonkuma · #11 (**permalink**) 03-07-11, 10:50

A function LPAD is supported from DB2 Version 9.7 for LUW or DB2 Version 9.1 for z/OS.

If you are using older DB2 version,
a temporary soution may be to use an UDF something like a sample in this site...
(Although, it was old wrok, I sometimes referred to the site.)
Sample UDFs for Migration

Or use an expression like...

Code:

SUBSTR('         ' || column_name , LENGTH(column_name) + 1 , 9)

If data type of the column_name is fixed length string(i.e. CHAR(n)),
use an expression like...

Code:

SUBSTR('         ' || column_name , LENGTH( RTRIM(column_name) ) + 1 , 9)

mlybarger · #12 (**permalink**) 03-07-11, 13:02

thanks, tonkuma, that seems to work for me. it seems rather kludgy that i have to code it that way, but it works.

Internet Services

Web Development

Digital Marketing

Consumer Tech