I'd solve it in this way:
Order By Case When :Sort = 'A' then OrdDat End Asc, Case When Sort = 'D'
Then OrdDat End Desc, Case When Sort Not In ('A', 'D') Then REFKL End
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
Modernization ? Education ? Consulting on IBM i
Database and Software Architect
IBM Champion since 2020
"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)
"Learning is experience ? everything else is only information!" (Albert
Einstein)
-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Martijn
van Breden
Sent: Tuesday, 3 September 2024 14:15
To: rpg400-l@xxxxxxxxxxxxxxxxxx
Subject: Case in Order-by clause
Hi all
I want to use a simple example of case logic in a query for educational
purposes, but I can't get it to work, so please help the trainer ('cause
he's the expert, ahum...)
I tried this in all kinds of forms in ACS, but it keeps (at best) ignoring
the order by and present me the first 10 records based on RRN
select REFKL, ORDAT, ORDNR
from TOVF
order by
case :SortCode
when 'A' then ' ORDAT asc '
when 'D' then ' ORDAT desc '
else ' REFKL asc '
end
limit 10;
I've googled around and found some examples resembling my approach, but not
exactly the same. Most use an index number to designate a sort column from
the select clause and don't bother about sorting sequence.
I also tried to move the ORDAT out of the case expression but that generates
an error right away.
The same happens when I remove the apostrophs surrounding ' ORDAT asc' and
the other result expressions.
I know other ways to get to the desired functional result but wanted to show
the elegant simplicity of this construction.
What am I missing?
Kind regards, Met vriendelijke groet,
Martijn van Breden
lead software architect
--
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@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
midrange.com
