AW: SQL Statement 'ORDER BY'

Hi Ron,

It's not necessary to use dynamic SQL (AKA Create String PREPARE DELARE).
It can be done with static SQL as follows:

Let's assume there is a field (UsrSort) that contains the sort sequence the
user selected,
i.e. 1 = ClientNo, 2 = ItemNo, 3 = OrderNo ...
(it does not matter how this field is defined)

In your embedded SQL-Statement you can use it as follows:
C/EXEC SQL
C+ Declare CsrNo1 Cursor for
C+     Select   Field1, Field2, .... FieldN
C+        from  MyFile
C+        Where ....
C+        Order By Case When :UsrSort = 1 Then ClientNo Else NULL End,
C+                 Case When :UsrSort = 2 Then ItemNo   Else NULL End,
C+                 Case When :UsrSort = 3 Then OrderNo  Else NULL End,
C+                 ...
C/End-Exec

Mit freundlichen Gruessen / Best regards

Birgitta

"Shoot for the moon, even if you miss, you'll land among the stars."
(Les Brown)

-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]Im Auftrag von Ronald Newman
Gesendet: Thursday, August 31, 2006 23:13
An: rpg400-l@xxxxxxxxxxxx
Betreff: SQL Statement 'ORDER BY'


I have a question about embedded SQL in an RPGLE program.   The requirement
of the application is to display a set of data on a screen and then let the
user select which field to sort on and then display the data in that order.
I know that I could create a logical file using each possible selection.
However this seems to be a lot of over head.  So I thought I would use SQL
and on the ?ORDER BY? statement I would use a variable and fill that with
the literal I wanted to sort on like you can do with most of the statements
in SQL.  However by my research it looks like you must use either the field
name or the position in the file.  Has anyone ever done anything like this
and if so can you give me some suggestions?

  Thanks
  Ron




---------------------------------
Get your own web address for just $1.99/1st yr. We'll help. Yahoo! Small
Business.
--
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.