Fixed List Select Syntax and Example Explanation

The EXECUTE IMMEDIATE and EXECUTE with PREPARE could not be used for SELECT query. For the SELECT query, we have a fixed list SELECT in which the column to be fetched remains fixed and it cannot be changed.

For example, if we want to select the orders placed on 14-08-2020. Then we can use dynamic SQL as given below:

Example

MOVE ‘SELECT ORDER_ID FROM ORDERS WHERE ORDER_DATE=?’ TO WS-SQL-QUERY
EXEC SQL
   DECLARE ORDER-CUR CURSOR FOR SELQUERY
END-EXEC
EXEC SQL
   PREPARE SELQUERY FROM :WS-SQL-QUERY
END-EXEC
MOVE ‘14-08-2020’ TO WS-ORDER-DATE
EXEC SQL
   OPEN ORDER-CUR USING :WS-ORDER-DATE
END-EXEC
PERFORM UNTIL SQLCODE = +100
   EXEC SQL
      FETCH ORDER-CUR INTO :WS-ORDER-ID
   END-EXEC
END-PERFORM
EXEC SQL
   CLOSE ORDER-CUR
END-EXEC