Re: Case in Order-by clause

I tried it, but it isn't accepting the second "case" for the sort direction ( SQL-status: 42601 Leverancierscode: -199).

When I only use the column names in a single case expression, it works for sorting by the right column, but of course without the sorting direction.


Met vriendelijke groet,



Martijn van Breden

lead software architect




Try this


select REFKL, ORDAT, ORDNR
from TOVF
order by
case :SortCode
when 'A' then ORDAT
when 'D' then ORDAT
else REFKL
end
case :SortCode
when 'A' then asc
when 'D' then desc
else asc
end
limit 10;

Martijn van Breden <m.vanbreden@xxxxxxxxxxxxxxxxxxxxxxxxxx> schrieb am Di.,
3. Sept. 2024, 14:15:

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.

--
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.