Re: Case in Order-by clause

this code should work 100%

SELECT REFKL, ORDAT, ORDNR
FROM TOVF
ORDER BY
CASE :SortCode
WHEN 'A' THEN ORDAT
WHEN 'D' THEN ORDAT * -1 -- For descending order
ELSE REFKL
END ASC
________________________________________________________________________
Freundliche Grüsse

Faisal Araya
Moosweg 3, 9240 Uzwil
Tel. : +41 (0)76 515 63 89
Email: erifam@xxxxxxxxx

Am Di., 3. Sept. 2024 um 15:02 Uhr schrieb Martijn van Breden
<m.vanbreden@xxxxxxxxxxxxxxxxxxxxxxxxxx>:

During normal execution in RSS it doesn't give an error, with visual explain it returns Sqlstate 57005 with SqlCode -666. Reading the docs that seems to have to do more with visual explain than with the actual execution.


Met vriendelijke groet,



Martijn van Breden

lead software architect




Hi Martijn

What is the SQLCOD or the SQLSTATE you get - in RSS, I expect you would
see something in the Messages tab, because you might be getting only a
warning, not an error. If you don't see it in RSS, there should be some
SQLxxxx message in the job log for the RSS job.

Regards
Vern

On 9/3/2024 7:15 AM, Martijn van Breden wrote:

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.