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 privileges that are held by the authorization ID of the owner of the plan or package must include at least one of the following for every table and view identified in the statement:
  • 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

Read syntax diagram
>>-+-----------------------------------+--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-'           
Notes:
  1. 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

Example 1: Put the maximum salary in DSN8A10.EMP into the host variable MAXSALRY. 
   EXEC SQL SELECT MAX(SALARY)
     INTO :MAXSALRY
     FROM DSN8A10.EMP;
Example 2: Put the row for employee 528671, from DSN8A10.EMP, into the host structure EMPREC. 
   EXEC SQL SELECT * INTO :EMPREC
     FROM DSN8A10.EMP
     WHERE EMPNO = '528671'
   END-EXEC.
Example 3: Put the row for employee 528671, from DSN8A10.EMP, into the host structure EMPREC. Assume that the row will be updated later and should be locked when the query executes. 
   EXEC SQL SELECT * INTO :EMPREC
     FROM DSN8A10.EMP
     WHERE EMPNO = '528671'
     WITH RS USE AND KEEP EXCLUSIVE LOCKS
   END-EXEC.