IBM Mainframe Forum

WHERE
(part 1) OPT_FLD1
           BETWEEN :WS-OPT-FID1-FROM AND :WS-OPT-FID1-TO
(part 2) AND OPT_FLD2
           BETWEEN :WS-OPT-FID2-FROM AND :WS-OPT-FID2-TO

Fixed-List Select Statements
A fixed-list SELECT statement is a statement that returns a predictable number of data items of a known type from an open cursor. For each of these items, a host variable is required to accommodate incoming data.

To issue a fixed-list SELECT statement dynamically:

(Optional) Test each SQL statement using Interactive SQL to see that proper results are obtained.
Load the SQL statement into a data area.
Use PREPARE to validate the statement and translate it into an executable form.
Use DECLARE to declare a cursor for the statement.
Use OPEN to open the cursor.
Use FETCH to retrieve a row into a fixed list of variables.
When the end of data is reached, use CLOSE to close the cursor.
Be sure to handle any errors.

The following example shows how to execute a fixed-list SELECT statement dynamically.

* include SQLDA copybook
     EXEC SQL
         INCLUDE SQLDA
     END-EXEC

* assign value to variable sqlcmd.
     MOVE 'select e_no, lname from employee where dept="1050"' TO sqlcmd.

* prepare SELECT command.
     EXEC SQL
         PREPARE q1 FROM :sqlcmd
     END-EXEC

* declare cursor for SELECT command.
     EXEC SQL
         DECLARE c1 CURSOR FOR q1
     END-EXEC

* open cursor.
     EXEC SQL
         OPEN c1
     END-EXEC

* fetch data into program variables EMPNO, LASTNME and FIRSTNME.
     EXEC SQL
         FETCH c1 INTO :EMPNO, :LASTNME
     END-EXEC

* close cursor.
     EXEC SQL
         CLOSE c1
     END-EXEC

IF OPT-FLD1 = SPACES
    MOVE SPACES TO OPT-FLD1-TEMP (a working storage temporary variable)
ELSE
    MOVE OPT-FLD1 TO OPT-FLD1-TEMP
END-IF

query:
WHERE MAN_FLD = WS-MAN-FLD
AND
OPT_FLD1 >= :OPT-FLD1-TEMP
AND
OPT_FLD2  >= :OPT-FLD2-TEMP
....