---

Return to the SQL Tips

---

Embedded SQL

1) Declare multiple cursors like C1cursor, C2cursor, etc.

2) Evaluate using PREPARE and EXECUTE to build an 'on-the-fly' SQL statement

3) Structure your SQL statement for maximum flexibility. In your example let's pretend you had the following:

C    SELECT
C    WHEN CHOICE = 'A'
C/EXEC SQL
C+ Select COL1, COL2, COL3
C+ from TABLEA
C+ where COL4=:X
C/END-EXEC
C    WHEN CHOICE='B'
C/EXEC SQL
C+ Select COL1, COL2, COL3
C+ from TABLEA
C+ where COL4 between :Y and :Z
C/END-EXEC
C    WHEN CHOICE='C'
C/EXEC SQL
C+ Select COL1, COL2, COL3
C+ from TABLEA
C/END-EXEC
C    ENDSL

Restructure this into:
C    SELECT
C    WHEN CHOICE = 'A'
C     Eval  Y=X
C     Eval  Z=X
C    WHEN CHOICE='B'
C     WHEN  CHOICE='C'
C     Eval  Y=*loval
C     Eval  Z=*hival
C    ENDSL
C/EXEC SQL
C+ Select COL1, COL2, COL3
C+ from TABLEA
C+ where COL4 between :Y and :Z
C/END-EXEC

In this case you would always use the BETWEEN instead of choosing. This same technique has been used by SQL and OPNQRYF.
Rob Berendt

[report a broken link by clicking here]

---