Re: Case in Order-by clause

[erifam@xxxxxxxxx]

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.