RE: Embedded SQL Order By with host variables

What about looking at dynamic preparation of the select? You could build a
character string and supply the value for the ORDER BY as a variable
something  like this:

C                   Select
  // Process sequence desired. Default is Empno
C                   When      NameOrder <> ' '
C                   Eval      Sequence = 'Lastname, Empno'
C                   Other
C                   Eval      Sequence = 'Empno'
C                   EndSl
  // Build SQL Select statement
C                   Eval      SqlString =
C                             'SELECT EMPNO, LASTNAME, WORKDEPT ' +
C                             'FROM EMPLOYEE ' +
C                             'WHERE WORKDEPT = ? ' +
C                             'ORDER BY ' + %TrimR(Sequence)
C/EXEC SQL
C+ PREPARE SQLSTMT FROM :SQLSTRING
C/END-EXEC

Bob

Bob Slaney/Atlanta/IBM@IBMUS (Lotus Notes)
Internet id: rjslaney@us.ibm.com
VM id: DALVM1.RJSLANEY@VM

Tieline  445-7374 Direct (770)835-7374
FAX tie 445-7579 Direct (770)835-7579
IBM Learning Services
iSeries Courseware Developer
IBM Corp.
3200 Windy Hill Road
Atlanta, GA 30339
WG07C13



                      "Leland, David"
                      <dleland@Harter.c        To:       
"'rpg400-l@midrange.com'" <rpg400-l@midrange.com>
                      om>                      cc:
                      Sent by:                 Subject:  RE: Embedded SQL Order 
By with host variables
                      rpg400-l-admin@mi
                      drange.com


                      10/29/2002 04:09
                      PM
                      Please respond to
                      rpg400-l





This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
--
[ Picked text/plain from multipart/alternative ]
What release of the OS are you one?  The SEU syntax checker in V5R1 doesn't
like host variables in the Order By clause.

Dave

-----Original Message-----
From: darren@dekko.com [mailto:darren@dekko.com]
Sent: Tuesday, October 29, 2002 3:54 PM
To: rpg400-l@midrange.com
Subject: Embedded SQL Order By with host variables



I have a rather large SQL statement and I'm wondering about host variables
in the ORDER BY clause because I'd rather not copy it a number of times to
just order it differently.  I can't find any IBM documentation describing
using host variables to specify an ORDER BY clause, but the Precompiler
seems to allow it, and the query optimizer said something about host
variables in the order by clause being a reason that an ODP might not be
re-used.  I figured from this that it was permissible.  It seems to just
whatever entry is in the host variable.

Anybody here had success or seen something about this?

Thanks in advance.

simplified example:
______________________________________
Eval  SORT1='FAC'

/EXEC SQL
SELECT PROD, CLASS, FAC
FROM IMAST
ORDER BY PROD, :SORT1
/END-EXEC
____________________________________
I've also tried:
Eval  SORT1=2

/EXEC SQL
SELECT PROD, CLASS, FAC
FROM IMAST
ORDER BY PROD, :SORT1
/END-EXEC
________________________________________


_______________________________________________
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/rpg400-l
or email: RPG400-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.
_______________________________________________
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/rpg400-l
or email: RPG400-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.