RE: SQL - Using Case in Order By clause

It is.  You have to set up another CASE statement, and you may need to set
up a dummy field *without a comma* at the end of the ORDER BY clause.
You'll need commas on your CASE statements so the expression creates
properly.

Also, you can't mix data types within one CASE statement; you have to use
one CASE for alpha's, another for numeric's, etc.

CASE and ORDER BY >= V5R2 only...

Regards,
Reeve Fritchman
Ayers Rock Software LLC
4915 S. West Shore Boulevard
Tampa, Florida 33611-3329
(813) 831-8574 (voice)
(813) 832-6391 (fax)


 


> -----Original Message-----
> From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-
> bounces@xxxxxxxxxxxx] On Behalf Of THarteau@xxxxxxxxxxxxxxxxxx
> Sent: Wednesday, June 23, 2004 9:13 AM
> To: RPG programming on the AS400 / iSeries
> Subject: RE: SQL - Using Case in Order By clause
> 
> 
> Hi,
>       Is it possible to order by more than one fields when you use case in
> SQL?  In all the examples I found, it only had one field on each When.
> For
> example, this works: (The file has fields for entire name, last name &
> first name. I am printing labels, so only need the entire name to print,
> but separate fields for sorting)
> 
>        C/EXEC SQL DECLARE A CURSOR FOR
>        C+ SELECT EMEMP#,EMPNAM,EMSTCD,EMOHYY,EMCORP,EMPAY#
>        C+ FROM HRV6DTA/HRPEMP
>        C+ ORDER BY Case       When :F1Sort = 'Y' then EMEMP#
>        C+                           When :F1Sort = 'N' then EMLNAM
>        C+                           End
>        C/END-EXEC
> 
> But I can't seem to get SEU to accept this:
> 
>       C/EXEC SQL DECLARE A CURSOR FOR
>       C+ SELECT EMEMP#,EMPNAM,EMSTCD,EMOHYY,EMCORP,EMPAY#
>       C+ FROM HRV6DTA/HRPEMP
>       C+ ORDER BY Case  When :F1Sort = 'Y' then EMEMP#
>       C+                      When :F1Sort = 'N' then EMLNAM, EMFNAM
>       C+                      End
>       C/END-EXEC
> 
> I get the message   'Token , was not valid. Valid tokens: END. '
> If this doesn't work, I was going to try to concatenate the last name
> and
> first name and use that to sort. Any ideas?
> 
> <===================================================>
> 
> Terri Harteau
> Felker Brothers Corporation
> ****************
> Paperwork is like kudzu. Both need to be pruned regularly. Usually, a
> flamethrower is overkill.      Usually.
> ****************
> 
> 
> 
> 
> 
> 
> --
> This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
> list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.