RE: SQL - Using Case in Order By clause

CASE isn't the same as an IF statement.  You can't use it to conditionally 
change the format of the statement.   CASE will return a single value based on 
different conditions.  Think of it more as substituting one value for another.

Rick

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of
THarteau@xxxxxxxxxxxxxxxxxx
Sent: Wednesday, June 23, 2004 8: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.