Dynamic SELECT statement Format 2 (Retrieve dynamically)

Function

The dynamic SELECT statement uses a list for retrieving data from a table.

Privileges

The owner of a list can use that list to retrieve data from tables.

Format 2: Retrieving table data using a list

(Cursor-Specification-Format-2)
 SELECT {{value-expression|WRITE specification|GET_JAVA_STORED_ROUTINE_SOURCE specification}
     [[AS]column-name]
        [, {value-expression|WRITE specification|GET_JAVA_STORED_ROUTINE_SOURCE specification}
        [[AS]column-name]]...
     |*}
   FROM LIST list-name
(Lock-Option)
[ {WITH {SHARE|EXCLUSIVE} LOCK
     |WITHOUT LOCK [ {WAIT|NOWAIT} ]}]
[{WITH ROLLBACK|NO WAIT} ]

Operands

For cursor specification, see 2.1.2 Cursor specification: Format 2; for the lock option, see 2.19 Lock option.

Specifies that the base table to be searched is to be locked when retrieval is performed using a list.

Common rules

  1. If a row that existed in the base table when the list was created is not found during retrieval processing, SQL code +110 is returned and retrieval processing continues.
  2. The same user cannot manipulate a list by connecting to HiRDB concurrently in multiple sessions.

Notes

  1. Rows in the base table that are deleted after the list is created cannot be retrieved during retrieval processing.
  2. Rows in the base table that are updated after the list is created can be retrieved.
  3. If rows are deleted and then rows are inserted into the base table after the list has been created, sometimes only inserted rows are retrieved.
  4. Between the time an SQL for searching a table via a list is preprocessed by the PREPARE statement and the time the OPEN statement is executed, an ASSIGN LIST statement that specifies the same list name must not be executed.