SELECT INTO
The SELECT INTO statement produces a result table that contains at most one row. The statement assigns the values in that row to host variables. If the table is empty, the statement does not assign values to the host variables.
Invocation
This statement can only be embedded in an application program. It is an executable statement that cannot be dynamically prepared.
Authorization
- The SELECT privilege on the table or view
- Ownership of the table or view
- DBADM authority for the database (tables only)
- DATAACCESS authority
- SYSADM authority
- SYSCTRL authority (catalog tables only)
If the SELECT INTO statement includes an SQL data change statement, the privilege set must also include at least the privileges (INSERT, UPDATE, or DELETE) that are associated with that SQL data change statement on the table or view.
Syntax
>>-+-----------------------------------+--select-clause--INTO---> | .-,-----------------------. | | V | | '-WITH----common-table-expression-+-' .-,-------------. V | >----host-variable-+--from-clause--+--------------+-------------> '-where-clause-' >--+-----------------+--+---------------+-----------------------> '-group-by-clause-' '-having-clause-' .--------------------------. V (1) | >--+-----------------+--------+------------------+-+------------> '-order-by-clause-' +-isolation-clause-+ '-SKIP LOCKED DATA-' >--+------------------+-----------------------------------------> '-QUERYNO--integer-' >--+------------------------------------+---------------------->< | .-1-. | '-FETCH FIRST--+---+--+-ROW--+--ONLY-' '-ROWS-'
- The same clause must not be specified more than once.
Description
The result table is derived by logically evaluating the isolation-clause, from-clause, where-clause, group-by-clause, having-clause, order-by-clause, and the select-clause, in this order. See Queries for a description of these clauses.
The tables or views identified in the statement can exist at the current server or at any DB2® subsystem with which the current server can establish a connection.
- WITH common-table-expression
- Refer to common-table-expression for information about specifying a common-table-expression.
- INTO host-variable,...
- Each host-variable must
identify a structure or variable that is described in the program
in accordance with the rules for declaring host structures and variables.
In the operational form of the INTO clause, a reference to a structure
is replaced by a reference to each of its host variables.
The first value in the result row is assigned to the first variable in the list, the second value to the second variable, and so on. If the number of host variables is less than the number of column values, the value W is assigned to the SQLWARN3 field of the SQLCA. (See SQL communication area (SQLCA).)
The data type of a variable must be compatible with the value assigned to it. If the value is numeric, the variable must have the capacity to represent the integral part of the value. For a date or time value, the variable must be a character string variable of a minimum length as defined in Datetime assignments. If the value is null, an indicator variable must be specified.
Each assignment to a variable is made according to the rules described in Language elements. Assignments are made in sequence through the list.
If an error occurs as the result of an arithmetic expression in the SELECT list of a SELECT INTO statement (division by zero or overflow) or a numeric conversion error occurs, the result is the null value. As in any other case of a null value, an indicator variable must be provided and the main variable is unchanged. In this case, however, the indicator variable is set to -2. Processing of the statement continues as if the error had not occurred. (However, this error causes a positive SQLCODE.) If you do not provide an indicator variable, a negative value is returned in the SQLCODE field of the SQLCA. Processing of the statement terminates when the error is encountered.
If an error occurs, no value is assigned to the host variable or to later variables, though any values that have already been assigned to variables remain assigned.
If an error occurs because the result table has more than one row, values may or may not be assigned to the host variables. If values are assigned to the host variables, the row that is the source of the values is undefined and not predictable.
- isolation-clause
- Specifies the isolation level at which the statement is executed and, optionally, the type of locks that are acquired.
- SKIP LOCKED DATA
- Specifies
that rows are skipped when incompatible locks are held on the row
by other transactions. These rows can belong to any accessed table
that is specified in the statement. SKIP LOCKED DATA can be used only
when isolation CS or RS is in effect and applies only to row level
or page level locks.
SKIP LOCKED DATA is ignored if it is specified when the isolation level that is in effect is repeatable read (WITH RR) or uncommitted read (WITH UR).
- QUERYNO integer
- Specifies
the number to be used for this SQL statement in EXPLAIN output and
trace records. The number is used for the QUERYNO columns of the plan
tables for the rows that contain information about this SQL statement.
This number is also used in the QUERYNO column of the SYSIBM.SYSSTMT
and SYSIBM.SYSPACKSTMT catalog tables.
If the clause is omitted, the number associated with the SQL statement is the statement number assigned during precompilation. Thus, if the application program is changed and then precompiled, that statement number might change.
Using the QUERYNO clause to assign unique numbers to the SQL statements in a program is helpful:
- For simplifying the use of optimization hints for access path selection
- For correlating SQL statement text with EXPLAIN output in the plan table
For information on using optimization hints, such as enabling the system for optimization hints and setting valid hint values, and for information on accessing the plan table, see Managing DB2 Performance.
- FETCH FIRST ROW ONLY integer
- The FETCH FIRST ROW ONLY clause can be used in the SELECT INTO
statement when the query can result in more than a single row. The clause indicates that only one
row should be retrieved regardless of how many rows might be in the result table. When a number is
explicitly specified, it must be 1.
Using the FETCH FIRST ROW ONLY clause to explicitly limit the result table to a single row provides a way for the SELECT INTO statement to be used with a query that returns more than a single row. Using the clause helps you to avoid using a cursor when you know that you want to retrieve only one row. To influence which row is returned, you can use the order-by-clause. When you specify order-by-clause, the rows of the result are ordered and then the first row is returned. If the FETCH FIRST ROW ONLY clause is not specified and the result table contains more than a single row, an error occurs.
Notes
- Default encoding scheme:
- The default encoding scheme for the data is the value in the bind option ENCODING, which is the option for application encoding. If this statement is used with functions such as LENGTH or SUBSTRING that are operating on LOB locators, and the LOB data that is specifies by the locator is in a different encoding scheme from the ENCODING bind option, LOB materialization and character conversion occur. To avoid LOB materialization and character conversion, select the LOB data from the SYSIBM.SYSDUMMYA, SYSIBM.SYSDUMMYE, or SYSIBM.SYSDUMMYU sample table.
- If the result table is empty:
- If the table is empty, the statement assigns +100 to SQLCODE, '02000' to SQLSTATE, and does not assign values to the host variables.
- Number of rows inserted:
- If the SELECT INTO statement of the cursor contains an SQL data change statement, the SELECT INTO operation sets SQLERRD(3) to the number of rows inserted.
- Using locators:
- Normally, you use LOB locators to assign and retrieve data from LOB columns. However, because of compatibility rules, you can also use LOB locators to assign data to host variables with CHAR, VARCHAR, GRAPHIC, or VARGRAPHIC data types.
Examples
EXEC SQL SELECT MAX(SALARY)
INTO :MAXSALRY
FROM DSN8A10.EMP;
EXEC SQL SELECT * INTO :EMPREC
FROM DSN8A10.EMP
WHERE EMPNO = '528671'
END-EXEC.
EXEC SQL SELECT * INTO :EMPREC
FROM DSN8A10.EMP
WHERE EMPNO = '528671'
WITH RS USE AND KEEP EXCLUSIVE LOCKS
END-EXEC.