Single-row SELECT statement (Retrieve one row)
Function
The single-row SELECT statement retrieves table data by fetching only one row of data without using a cursor.
Although the single-row SELECT statement has the same operands as the SELECT clause with a query specification, unlike the SELECT clause as a statement in a query specification it does not operate on sets.
The single-row SELECT statement includes the INTO clause that specifies the area for receiving the retrieved results.
Privileges
Same as the SELECT clause as a statement in a query specification
Format: Fetching up to one row of data into specified embedded variables
SELECT [{ALL|DISTINCT}] {selection-expression
[, selection-expression]...|*}
[INTO {:embedded-variable[:indicator-variable]
|[statement-label.]SQL-variable-name
|[[authorization-identifier.]routine-identifier.]
SQL-parameter-name
|[statement-label.]SQL-variable-name..attribute-name
[..attribute-name]...
|[[authorization-identifier.]routine-identifier.]
SQL-parameter-name
..attribute-name[..attribute-name]...}
[, {:embedded-variable[:indicator-variable]
|[statement-label.]SQL-variable-name
|[[authorization-identifier.]routine-identifier.]
SQL-parameter-name
|[statement-label.]SQL-variable-name..attribute-name
[..attribute-name]...
|[[authorization-identifier.]routine-identifier.]
SQL-parameter-name
..attribute-name[..attribute-name]...} ]...]
(Table-Expression)
FROM table-reference [, table-reference]...
[WHERE search-condition]
[GROUP BY value-expression [, value-expression]...]
[HAVING search-condition]
(Lock-option)
[[{WITH {SHARE|EXCLUSIVE}LOCK
|WITHOUT LOCK [{WAIT|NOWAIT}]}]]
[{WITH ROLLBCK|NO WAIT}]
[FOR UPDATE [OF column-name[, column-name]...][NOWAIT]]
Operands
For the SELECT clause, see 2.3 Query specification; for table expressions, see 2.5 Table expressions; for the lock option, see 2.19 Lock option.
The INTO clause must always be specified when the SELECT statement is coded, either by itself or directly in a UAP or procedure.
However, the INTO clause cannot be specified in any of the following locations:
FOR UPDATE[OF column-name[, column-name]...] clause is called the FOR UPDATE clause.
FOR UPDATE [OF column-name[, column-name]...]
Must be specified in order to update, delete, or add a row using data retrieved from a single-line SELECT statement. Omit this operand for a table that is retrieved from a single-line SELECT statement if there are no rows to be updated, deleted, or added. If no lock option is specified in the SQL statement, the lock option is determined by the value specified in PDISLLVL or the value specified for the data guarantee level specified in SQL-compile-option. However, if YES is indicated for PDFORUPDATEEXLOCK or if FOR UPDATE EXCLUSIVE is specified after the data guarantee level in SQL-compile-option, the lock option for such a cursor is assumed to be WITH EXCLUSIVE LOCK. For details, see the HiRDB Version 9 UAP Development Guide.
[OF column-name[, column-name]...]
In general, this clause is optional.
Specifies columns that are not specified in the selection expression of a single-line SELECT statement. A column can be specified only once.
When a column name is specified, instead of using the column name that was specified in AS column-name, specify the column of the table that was specified in the FROM clause of the outermost query specification.
[NOWAIT]
The behavior is the same as if the FOR UPDATE clause was specified and the WITH EXCLUSIVE LOCK NO WAIT lock option was set. However, if Lock-option is specified, NOWAIT cannot be specified.
For details about operations when the WITH EXCLUSIVE LOCK NO WAIT lock option is specified, see 2.19 Lock option.
Common rules
Examples
See the section on DECLARE CURSOR Format 1 (Declare cursor) for examples.