What is Fixed-list SELECT? Give the syntax and explain with help of an example

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

Mandalika

e

Updated on: 30-Nov-2020

109 Views

Kickstart Your Career

Get certified by completing the course

Get Started