MVSFORUMS.com

[pkrish_18]

I'm running the following query..

SELECT *
FROM RBPRDB.T1508
WHERE SO_CD = 'EO1148HJ'
AND SI_NUM = 01
AND SD_NUM = 02
AND SSI_CD = 'A01'
ORDER BY MOT_MFG_DUNS_CD ASC
WITH UR;

I have the Primary key and Unique Index defined on the following columns

SO_CD
SI_NUM
SD_NUM
SSI_CD
KANBAN_CD

column MOT_MFG_DUNS_CD is a notnull column but has spaces.

1) The resultant of the above query (with order by clause) is

SO_CD SI_NUM SD_NUM SSI_CD KANBAN_CD
---------+---------+---------+---------+---------+---------+---------+---
EO1148HJ 1 2 A01 ECF22T05
EO1148HJ 1 2 A01 ECF22T04
EO1148HJ 1 2 A01 ECF22T01
EO1148HJ 1 2 A01 ECF22T03
EO1148HJ 1 2 A01 ECF22T02
DSNE610I NUMBER OF ROWS DISPLAYED IS 5


2) The resultant of the above query (without order by clause) is

SO_CD SI_NUM SD_NUM SSI_CD KANBAN_CD
---------+---------+---------+---------+---------+------
EO1148HJ 1 2 A01 ECF22T01
EO1148HJ 1 2 A01 ECF22T02
EO1148HJ 1 2 A01 ECF22T03
EO1148HJ 1 2 A01 ECF22T04
EO1148HJ 1 2 A01 ECF22T05

Note that MOT_MFG_DUNS_CD has spaces for all the 5 retrieved row.

Would like to know why there is a difference in the display order of the Kanban_CD .

[Manas Biswal]

pkrish_18,

In short, thats because DB2 optimizer decided that those were the two most efficient ways to retrieve the records for your queries (with and without the order by clause). How the optimizer decides that is a complex story, something that I also do not know everything about.
If you would like your query output to be ordered a certain way, sort on that column.

Regards,
Manas
_________________
There is no path to peace. Peace is the path.
- Mahatma Gandhi (1869-1948)