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:

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 9 UAP Development Guide.
If an authorization identifier is specified in the SQL procedure statement of a public procedure definition, specify upper-case PUBLIC enclosed in double quotation marks (") as the authorization identifier.
[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.
If an authorization identifier is specified in the SQL procedure statement of a public procedure definition, specify upper-case PUBLIC enclosed in double quotation marks (") as the authorization identifier.

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

  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 embedded variable is character data type that uses the default character set, and the retrieval result is character data type that uses a different character set than the embedded variable, it is automatically converted to the character set used for the embedded variable.
    • 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.
  4. The FOR UPDATE clause cannot be specified if any of the following are specified in the single-line SELECT statement or the lock option:
    (a) UNION[ALL], or EXCEPT[ALL]
    (b) A table specified in the FROM clause of the outermost query specification is specified in the FROM clause of a subquery
    (c) Joined tables specified in the outermost query specification
    (d) A derived table in a FROM clause specified in the outermost query specification
    (e) SELECT DISTINCT specified in the outermost query specification
    (f) A GROUP BY clause specified in the outermost query specification
    (g) A HAVING clause specified in the outermost query specification
    (h) A set function on the outermost query specification
    (i) The window function on the outermost query specification
    (j) Any of the following view tables in the FROM clause of the outermost query specification:
    • A view table defined by specifying any of items (a) to (i) above in a view definition statement
    • View tables that are defined by specifying a value expression, other than a column specification in the SELECT clause of the outermost query specification, in a view definition statement
    • A view table defined with the READ ONLY option specified in the view definition statement
    (k) WITHOUT LOCK NOWAIT

Examples

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