I'm not quite sure, but is the first column within your order by specification the date?
If not you have to specify the date field in your order by clause at the first position.
Let me guess, you specified a logical file in your SELECT statement and expected that the data is returned in the sequence of the logical file.
But ... if the sequence is taken it is nothing else than by accident.
The query optimizer rewrites all SELECT statements that point to DDS described logical files. It analyses the DDS description but takes only the selected fields, the join clauses and the select/omit clauses and rewrites the SELECT statement based on this information. It does not consider any key information. After having rewritten the SELECT statement the optimization starts. At this point the optimizer does not know anymore that you gave him an keyed logical file.
The optimizer decides if and which access path (index, logical file) is taken. Quite often it is faster to take an access path with a different sequence and sort the selected rows later.
The rule is: If you need a specific sequence you have to specify an ORDER BY (in the expected sequence).
If the sequence of the data is unimportant, do not specify an ORDER BY.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them and keeping them!"
„Train people well enough so they can leave, treat them well enough so they don't want to.“ (Richard Branson)
-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Jim Hawkins
Sent: Dienstag, 20. August 2019 16:07
To: rpg400-l@xxxxxxxxxxxxxxxxxx
Subject: Re: 1st RPG program with embedded SQL
I have results that look like this:
2019/03/25
2019/03/25
2019/03/25
2019/03/25
2019/03/25
2019/03/25
2019/03/25
2019/05/01
2019/05/01
2019/05/01
2019/03/26
2019/03/26
2019/03/26
When they are supposed to sorted by this date.
Luis, yes that was a typo.
I changed from an "inner join" to a simple "join" and that helped some, but I still getting results like above. My dates should be greater than 2019/08/15 however.
Steve, the example I was following (and I hate to introduce anything that may add issues) spelled out the files, maybe next time I will use the shorter qualifiers.
I will search for that join poster (just don't have a wall to post it on ☹ ).
Thanks everyone for the input on joining.
Regards,
Jim Hawkins
Programmer Analyst
Interkal LLC
Kalamazoo, MI
--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
midrange.com
