Forcing AS/400 query data to respect original data order

Tags:
*QRYDFN
AS/400 Query
QMQRY
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    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:
    AND ( T02.GJCDZA = '001367' AND ZONED(GJASZB/100), 5) BETWEEN 10901 AND 10912
    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
    125,585 pointsBadges:
    report
  • TomLiotta
    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
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following