Re: SQL "ORDER BY" and CASE

I've had little to no luck getting variable sort options to work. Although 
I do like the syntax of your attempt.  Normally, what I end up doing is 
using the SQL PREPARE and EXECUTE combination.

May I suggest that you submit a DCR?  It would be beneficial if you point 
out where this is documented that it works in other databases.
http://faq.midrange.com/data/cache/189.html

Rob Berendt
-- 
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





"ile rpg" <ile_rpg_guy@xxxxxxxxxxx> 
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
02/22/2004 02:43 PM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>


To
rpg400-l@xxxxxxxxxxxx
cc

Fax to

Subject
SQL "ORDER BY" and CASE






   I'm getting this error ("ORDER BY column *N or expression not in result
   table") in V5R1 using this SQLRPGLE code:
   select cmcust,cmname,cmcity from custmast 
   order by 
   (case 
   when :option = '*NAME' then cmname
   when :option = '*CITY' then cmcity
   end)
 
   The error is indicated at the "(case" statement, with or without
   parentheses.  The column names are valid; OPTION is a valid character
   variable.
 
   Is this construct not supported in iSeries DB2?  Or is my syntax just
   goofy?
 
   Thanks,
   irg

 ----------------------------------------------------------------------

   Store more e-mails with MSN Hotmail Extra Storage * 4 plans to choose
   from!
_______________________________________________
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.