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}]
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:
- A SELECT statement in an SQL statement preprocessed by the PREPARE statement
- A SELECT statement in an SQL statement preprocessed/executed by the EXECUTE IMMEDIATE statement
- A SELECT statement in a cursor declaration
- {: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]...} ]...
- embedded-variable
- Specifies an embedded variable into which a column value in the row is to be read.
- indicator-variable
- Specifies when the column value to be read into the embedded variable may be the null value.
- [statement-label.]SQL-variable-name
- [[authorization-identifier.]routine-identifier.]SQL-parameter-name
- Specifies either an SQL variable or an SQL parameter that is to receive the value of a column in a procedure. For details about specification values in a Java procedure, see JDBC drivers or SQLJ in the HiRDB Version 8 UAP Development Guide.
- [statement-label.]SQL-variable-name..attribute-name[..attribute-name]...
- [[authorization-identifier.] routine-identifier.]SQL-parameter-name attribute-name[..attribute-name]...
- These operands are specified to receive the values of attributes in a column.
Common rules
- When retrieval results are limited to no more than one row, the single-row SELECT statement can be used to retrieve the data without using a cursor but by specifying the INTO clause. If the retrieval results include more than one row, the single-row SELECT statement cannot be used.
- The number of retrieval result columns and the number of embedded variables specified in the INTO clause must agree. If they do not, the W warning flag is set in SQLWARN3 in the SQL Communications Area.
- The data type of each embedded variable specified in the INTO clause must be either the same as the data type of the corresponding column or a data type that can be converted into that data type.
- If the data fetched into a fixed-length character string (including a national character string or a mixed character string) embedded variable is shorter than the length of the retrieval item, the data is left-justified in the embedded variable and trailing blanks are added.
- If the value of a retrieval results column is the null, the value of the corresponding embedded variable is unpredictable.
- If the value of a retrieval results column is the null, an indicator variable must be specified.
- If there are no rows to be fetched, the system returns the following return codes:
- Return code 100 to SQLCODE in the SQL Communications Area
- Return code 100 to the SQLCODE variable
- Return code '02000' to the SQLSTATE variable
- UNION [ALL], EXCEPT [ALL], the ORDER BY clause, and the LIMIT clause can be specified according to the rules given in 2.1.1 Cursor specification: Format 1 and 2.3 Query specification.
If UNION [ALL]and EXCEPT [ALL] are specified, the INTO clause should be specified only once following the first occurrence of the SELECT clause.
Examples