Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2011
    Posts
    220

    Unanswered: Result set sorting

    Hi;

    Please find the below query

    DB2 9.1 Z/OS
    Code:
    SELECT INV_LOC
    ,INV_PRE
    ,INV_MID
    ,INV_LAS
    ,INV_DT
    FROM 
      MAIN_TAB
    
    WHERE
    
        INV_PRE >=:WS-INV-PRE
    AND INV_MID >=:WS-INV-MID
    AND INV_LAS >=:WS-INV-LAS
    
    ORDER BY
    
     INV_LOC
    ,INV_PRE
    ,INV_MID
    ,INV_LAS
    ,INV_DT DESC
    with out giving any values for WHERE clause,below result set
    is getting..it is fine
    Code:
    INV_LOC    INV_PRE  INV_MID  INV_LAS  INV_DT
    AAA         DE	     621      DD       2012-09-01
    BBB         AA       111      WE       2011-02-01
    CCC         AA       444      ER       2009-01-31
    But,If we give input as
    :WS-INV-PRE = AA
    :WS-INV-MID = 111
    :WS-INV-LAS = WE means
    it giving the same result set as above

    But

    EXPECTED RESULT SET IS
    Code:
    INV_LOC    INV_PRE  INV_MID  INV_LAS  INV_DT
    BBB         AA       111      WE       2011-02-01
    CCC         AA       444      ER       2009-01-31
    AAA         DE	     621      DD       2012-09-01
    pLEASE HELP

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    how did you get to this conclusion : EXPECTED RESULT SET IS...
    inv_loc is the firts col to sort on and not inv_pre
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Sep 2011
    Posts
    220
    To get the "EXPECTED RESULT SET",how to change the ORDER BY clause

    Please Help

  4. #4
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by Billa007 View Post
    To get the "EXPECTED RESULT SET",how to change the ORDER BY clause

    Please Help
    There's probably a million ways to get the expected result set. Order by INV_PRE, INV_MID is one example. Could you clarify what it is you are trying to do?
    --
    Lennart

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Maybe it's the inequality in "INV_PRE >=:WS-INV-PRE" which is confusing you?
    When the WHERE condition "INV_PRE >='AA' " is present, as it is in your second case, you'll get rows with INV_PRE equal to 'AA', but also rows where INV_PRE equals 'AB', 'AC', ..., 'BA', 'BB', ..., 'DE', ... since all are alphabetically larger than or equal to 'AA'.

    ORDER BY is only considered (and performed) *after* the filtering (the WHERE condition) is applied. And both work independently one from the other: it's not because you filter first on INV_PRE that the output will be sorted on that column; it's the first argument of ORDER BY which decides on the sorting order.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  6. #6
    Join Date
    Sep 2011
    Posts
    220
    Hi;

    I need the result set order based on the below columns while not getting values in the WHERE clause columns
    INV_LOC
    ,INV_PRE
    ,INV_MID
    ,INV_LAS
    ,INV_DT DESC


    If the below columns having the values in the WHERE clause means
    INV_PRE
    ,INV_MID
    ,INV_LAS

    the result set should be the sorted order based on the below columns

    INV_PRE
    ,INV_MID
    ,INV_LAS
    ,INV_LOC
    ,INV_DT DESC


    According to the values existency the ORDER BY clause to be executed..

    Please let me know,if any

  7. #7
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    specify any where clause and any order by clause needed..
    and also read the doc at
    Defining an order by operator
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  8. #8
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by Billa007 View Post
    Hi;

    I need the result set order based on the below columns while not getting values in the WHERE clause columns
    INV_LOC
    ,INV_PRE
    ,INV_MID
    ,INV_LAS
    ,INV_DT DESC


    If the below columns having the values in the WHERE clause means
    INV_PRE
    ,INV_MID
    ,INV_LAS

    the result set should be the sorted order based on the below columns

    INV_PRE
    ,INV_MID
    ,INV_LAS
    ,INV_LOC
    ,INV_DT DESC


    According to the values existency the ORDER BY clause to be executed..

    Please let me know,if any
    I'm not sure I understand, but if you need to sort on different columns dependent of some value(s) you can use a case expression like:

    Code:
    ORDER BY
        CASE WHEN ... THEN INV_PRE ELSE INV_MID END
       ,CASE WHEN ... THEN ... END
       , ...
    --
    Lennart

Posting Permissions

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