SELECT INTO statement
The SELECT INTO statement produces a result table consisting of at most one row, and assigns the values in that row to host variables.
If the table is empty, the statement assigns +100 to SQLCODE and '02000' to SQLSTATE and does not assign values to the host variables. If more than one row satisfies the search condition, statement processing is terminated, and an error occurs (SQLSTATE 21000).
Invocation
This statement can be embedded only in an application program. It is an executable statement that cannot be dynamically prepared.
Authorization
- SELECT privilege on the table, view, or nickname
- CONTROL privilege on the table, view, or nickname
- DATAACCESS authority
- WRITE privilege on the global variable that is not defined in a module
- EXECUTE privilege on the module of the global variable that is defined in a module
GROUP privileges are not checked for static SELECT INTO statements.
If the target of the SELECT INTO statement is a nickname, privileges on the object at the data source are not considered until the statement is executed at the data source. At this time, the authorization ID that is used to connect to the data source must have the privileges that are required for the operation on the object at the data source. The authorization ID of the statement can be mapped to a different authorization ID at the data source.
Syntax
Description
For a description of the common-table-expession, select-clause, from-clause, where-clause, group-by-clause, having-clause, order-by-clause, offset-clause, fetch-clause, and isolation-clause, see subselect.
- INTO assignment-target
- Identifies one or more targets for the assignment of output values.
The first value in the result row is assigned to the first target in the list, the second value to the second target, and so on. Each assignment to an assignment-target is made in sequence through the list. If an error occurs on any assignment, no value is assigned to any assignment-target.
When the data type of every assignment-target is not a row type, then the value 'W' is assigned to the SQLWARN3 field of the SQLCA if the number of assignment-targets is less than the number of result column values.
If the data type of an assignment-target is a row type, then there must be exactly one assignment-target specified (SQLSTATE 428HR), the number of columns must match the number of fields in the row type, and the data types of the columns of the fetched row must be assignable to the corresponding fields of the row type (SQLSTATE 42821).
If the data type of an assignment-target is an array element, then there must be exactly one assignment-target specified.
- global-variable-name
- Identifies the global variable that is the assignment target.
- host-variable-name
- Identifies the host variable that is the assignment target. For LOB output values, the target can be a regular host variable (if it is large enough), a LOB locator variable, or a LOB file reference variable.
- SQL-parameter-name
- Identifies the parameter that is the assignment target.
- SQL-variable-name
- Identifies the SQL variable that is the assignment target. SQL variables must be declared before they are used.
- transition-variable-name
- Identifies the column to be updated in the transition row. A transition-variable-name must identify a column in the subject table of a trigger, optionally qualified by a correlation name that identifies the new value.
- array-variable-name
- Identifies an SQL variable, SQL
parameter, or global variable of an array type.
- [array-index]
- An expression that specifies which element in the array will be the target of the assignment. For an ordinary array, the array-index expression must be assignable to INTEGER (SQLSTATE 428H1) and cannot be the null value. Its value must be between 1 and the maximum cardinality defined for the array (SQLSTATE 2202E). For an associative array, the array-index expression must be assignable to the index data type of the associative array (SQLSTATE 428H1) and cannot be the null value.
- field-reference
- Identifies the field within a row type value that is the assignment target. The field-reference must be specified as a qualified field-name where the qualifier identifies the row value in which the field is defined.
- FOR READ ONLY or FOR UPDATE
- Indicates the intended use for the selected row. The default is FOR
READ ONLY.
- FOR READ ONLY
- Specifies that the selected row will not be locked for update.
- FOR UPDATE
- Specifies that the selected row from the underlying table will be locked to facilitate updating the row later on in the transaction, similar to the locking done for the select statement of a cursor which includes the FOR UPDATE clause.
Rules
- Global variables cannot be assigned inside triggers that are not defined using a compound SQL (compiled) statement, functions that are not defined using a compound SQL (compiled) statement, methods, or compound SQL (inlined) statements (SQLSTATE 428GX).
Notes
- Syntax alternatives: For consistency
with SQL queries:
- FOR FETCH ONLY can be specified in place of FOR READ ONLY
Examples
- Example 1: This C example puts the maximum salary in the
EMP table into the host variable MAXSALARY.
EXEC SQL SELECT MAX(SALARY) INTO :MAXSALARY FROM EMP;
- Example 2: This C example puts the row for employee 528671
(from the EMP table) into host variables.
EXEC SQL SELECT * INTO :h1, :h2, :h3, :h4 FROM EMP WHERE EMPNO = '528671';
- Example 3: This SQLJ example puts the row for employee
528671 (from the EMP table) into host variables. That row will later
be updated using a searched update, and should be locked when the
query executes.
#sql { SELECT * INTO :FIRSTNAME, :LASTNAME, :EMPNO, :SALARY FROM EMP WHERE EMPNO = '528671' FOR UPDATE };
- Example 4: This C example puts the maximum salary in the
EMP table into the global variable GV_MAXSALARY.
EXEC SQL SELECT MAX(SALARY) INTO GV_MAXSALARY FROM EMP;