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:

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

  1. 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.
  2. 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
  3. 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

See the section on DECLARE CURSOR Format 1 (Declare cursor) for examples.