1. Registered User
Join Date
Sep 2011
Posts
220

Hi;

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```

2. Registered User
Join Date
Apr 2006
Location
Belgium
Posts
2,540
how did you get to this conclusion : EXPECTED RESULT SET IS...
inv_loc is the firts col to sort on and not inv_pre

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

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

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?

5. Registered User
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.

6. Registered User
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..

7. Registered User
Join Date
Apr 2006
Location
Belgium
Posts
2,540
specify any where clause and any order by clause needed..
and also read the doc at
Defining an order by operator

8. Registered User
Join Date
Mar 2003
Posts
280
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..

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

#### Posting Permissions

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