Results 1 to 12 of 12
  1. #1
    Join Date
    Dec 2010
    Posts
    8

    Unanswered: strange select results

    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?

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You are comparing strings not numbers. As a string '5000' is still > '11000'.

    Andy

  4. #4
    Join Date
    Dec 2010
    Posts
    8
    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'

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ..... 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?

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dav1mo View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    '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.
    Last edited by tonkuma; 03-04-11 at 23:54.

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  10. #10
    Join Date
    Dec 2010
    Posts
    8

    ipead study

    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.

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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)

  12. #12
    Join Date
    Dec 2010
    Posts
    8

    Right toieste

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •