Forcing AS/400 query data to respect original data order
000012 FROM DISACTDTA1/COGJREP T02,
000013 DISACTDTA1/COGKREP T01,
000014 DISACTDTA1/COCXREP T04,
000015 DISACTDTA1/COGDREP T03,
000016 SATACTDTA1/FINURED T05
000017 WHERE GJEFZC = GKEFZC
000018 AND GKB8ZC = CXB8ZC
000019 AND T02.GJAN8X = T03.GDAN8X
000020 AND T01.GKB8ZC = T05.NUB8ZC
000021 AND( T02.GJCDZA = '001367'
000022 AND ZONED(GJASZB/100), 5) BETWEEN 10901 AND 10912
This is the result of the IBM conversion tool of *QRYDFN in *QMQRY. The optimizer change the order of join files and the result data set is always smaller than the original. There is a mean to force it to respect the initial order like in ORACLE?
Software/Hardware used:
iSeries
ASKED:
February 16, 2010 9:06 PM
UPDATED:
February 17, 2010 1:23 PM
Answer Wiki:
It doesn't matter what order you do it in, the results will be the same.
You've defined an inner join - You will get a record from every record combination of the 5 files that pass the where tests.
Phil
To see all answers submitted to the Answer Wiki: View Answer History.
Technically, the conversion seems to be wrong or the original *QRYDFN is possibly wrong. The last two lines of what you show work out to be:
I don’t think that’s what you want at all… unless it somehow is figured out when the SQL executes.
Weird if it ignores the parentheses, though.
Tom
Personally, and just IMO, I’d throw the *QRYDFN away anyway. As long as you have QM queries, there doesn’t seem to be any point in keeping the old ones going. If they convert incorrectly, correct the converted source and make that the new starting point.
Tom