RE: Variable ORDER BY

You have to use:

a dynamic SQL statement.

 /free
   eval myStmt = 'select * from myfile '
                        +  'order by ' + myField
                        +  ' for read only';

 /end-free

c/exec SQL
c+  prepare statement S1 from :myStmt
c/end-exec

c/exec SQL
c+  delcare MyCursor cursor for S1
c/end-exec

c/exec SQL
c+  open MyCursor
c/end-exec


or with v5r2 you can use a case statment in the order by

C/EXEC SQL              
C+ Declare MyCursor Cursor For
C+   Select *           
C+     From Myfile     
C+     Order By
C+      case :MyField
C+        when 'NAME1' then NAME1
C+        when 'NAME2' then NAME2
C+        else NAME3
C+      end
C+   For Read Only      
C/End-Exec              


Charles Wilt
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
 

> -----Original Message-----
> From: michael@xxxxxxxxxxxxxxxxxx [mailto:michael@xxxxxxxxxxxxxxxxxx]
> Sent: Wednesday, December 08, 2004 3:42 PM
> To: midrange-l@xxxxxxxxxxxx
> Subject: Variable ORDER BY
> 
> 
> How can I use a  variable ORDER BY clause in an embedded SQL 
> statement?
> This doesn't work...
> 
> C/EXEC SQL              
> C+ Declare MyCursor Cursor For
> C+   Select *           
> C+     From Myfile     
> C+     Order By :MyField   
> C+   For Read Only      
> C/End-Exec              
> 
> I know I have to do something different because it's a variable that
> contains a field name, but I dunno what that different thing is.
> 
> TIA...
> 
> 
> --
> This is the Midrange Systems Technical Discussion 
> (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>