RE: Using Embedded SQL

Here is some code for creating a dynamic select statement.
This example uses pre-selected fields but variable where and order by
clauses.
Changing the fields would be a little different.

D dyns            s            256a
D a               s              1a   inz('''')
D select          c                   const('SELECT PN,-
d                                      CATNO,DRAWNG,OLDPN FROM PNMAST')
D w1              c                   const(' WHERE CATNO  >= ')
D w2              c                   const(' WHERE DRAWNG >= ')
D w3              c                   const(' WHERE OLDPN >= ')
d o1              c                   const('  ORDER BY CATNO')
d o2              c                   const('  ORDER BY DRAWNG')
d o3              c                   const('  ORDER BY OLDPN')
D rrn             s              3  0
D RPT             DS
D  PN                     1      7  0
D  CATNO                  8     32
D  DRAWNG                33     40
D  OLDPN                 41     65
C/EXEC SQL WHENEVER SQLERROR GOTO ERROR
C/END-EXEC
  *** the variable IN is a character field to be appended to the where
clause.
  *** the variable A puts in the double quotes since the variable is
character.
C     OPT           IFEQ      '1'
c                   eval      dyns = select + w1 + a + in + a + o1
C                   END
C     OPT           IFEQ      '2'
c                   eval      dyns = select + w2 + a + in + a + o2
C                   END
C     OPT           IFEQ      '3'
c                   eval      dyns = select + w3 + a + in + a + o3
C                   END
C/EXEC SQL
C+  PREPARE STMT FROM :DYNS
C/END-EXEC
C/EXEC SQL
C+  DECLARE C1 CURSOR FOR STMT
C/END-EXEC
C*
 C/EXEC SQL WHENEVER NOT FOUND GO TO FULL
 C/END-EXEC
 C*
 C/EXEC SQL OPEN C1
 C/END-EXEC
 C*
 C     SQLCOD        DOUNE     0
 C/EXEC SQL
 C+   FETCH C1 INTO :RPT
 C/END-EXEC
                Do something
 c                           END



 -----Original Message-----
From:   Giri Gopal [mailto:GiriGopal@bcginK.com]
Sent:   Tuesday, August 21, 2001 7:50 AM
To:     rpg400-l@midrange.com
Subject:        Using Embedded SQL

Hello:

I have a few questions using Embedded SQL in RPG ILE scenario