Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2011
    Posts
    16

    Question Unanswered: Sql query giving different output.

    Hi,

    Below query is giving deferent output
    on DB2 v9.1 and v9.7.

    db2 "select case when 1=0 then
    'Success' else 'Fail' end||'XXX' from dual"

    Output on v9.7:

    1
    ----------
    Fail XXX

    1 record(s) selected.

    Output on v9.1:

    1
    ----------
    FailXXX

    1 record(s) selected.

    There
    are extra spaces coming in the returned string in v9.7 which is causing
    the application to fail in some cases.

  2. #2
    Join Date
    Sep 2011
    Posts
    16
    I also can use db2 "select case when 1=0 then trim('Success') else trim('Fail') end||'XXX' from dual"
    But i have to do changes in application which is not preferable.

    Ashish

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What do you get with this:

    Code:
    select case when 1=0 then 'Success' else 'Fail' end || 'XXX' from sysibm.sysdummy1
    Andy

  4. #4
    Join Date
    Sep 2011
    Posts
    16
    This is also giving same result.

    db2 "select case when 1=0 then 'success' else 'fail' end || 'end' from sysibm.sysdummy1"

    1
    ----------
    fail end


    Ashish

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Interesting. On my V9.7 DB, I get this:

    Code:
    >select case when 1=0 then 'Success' else 'Fail' end || 'XXX' from sysibm.sysdummy1
    
    1
    ----------
    FailXXX
    I wonder if it an Oracle compatibility issue? (I have it turned off)

    Andy

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Works fine on DB2 9.7.5 with Oracle compatibility:
    Code:
    [db2inst9@oc2522778176 ~]$ db2 connect to fedtest
    
       Database Connection Information
    
     Database server        = DB2/LINUXX8664 9.7.5
     SQL authorization ID   = DB2INST9
     Local database alias   = FEDTEST
    
    [db2inst9@oc2522778176 ~]$ db2 "select case when 1=0 then 'success' else 'fail' end || 'end' from sysibm.sysdummy1"
    
    1         
    ----------
    failend   
    
      1 record(s) selected.
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    Sep 2011
    Posts
    16
    Thanks to all.

    The issue is resolved as the DB2_COMPATIBILITY_VECTOR was set to ORA.
    I just changed and problem solved.

    Ashish

Posting Permissions

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