RE: Variable ORDER BY

Very cool...thanks Charles.

> -------- Original Message --------
> Subject: RE: Variable ORDER BY
> From: CWilt@xxxxxxxxxxxx
> Date: Wed, December 08, 2004 3:57 pm
> To: midrange-l@xxxxxxxxxxxx
> 
> 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.
> > 
> --
> 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.