Result set sorting

Billa007 · #1 (**permalink**) 08-13-12, 09:32

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

przytula_guy · #2 (**permalink**) 08-13-12, 09:38

how did you get to this conclusion : EXPECTED RESULT SET IS...
inv_loc is the firts col to sort on and not inv_pre

Billa007 · #3 (**permalink**) 08-13-12, 10:03

To get the "EXPECTED RESULT SET",how to change the ORDER BY clause

Please Help

lelle12 · #4 (**permalink**) 08-13-12, 10:11

Quote:

Originally Posted by Billa007

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?

Peter.Vanroose · #5 (**permalink**) 08-14-12, 03:58

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.

Billa007 · #6 (**permalink**) 08-14-12, 04:06

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

przytula_guy · #7 (**permalink**) 08-14-12, 04:08

specify any where clause and any order by clause needed..
and also read the doc at
Defining an order by operator

lelle12 · #8 (**permalink**) 08-14-12, 04:57

Quote:

Originally Posted by Billa007

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
   , ...

Internet Services

Web Development

Digital Marketing

Consumer Tech