SELECT INTO statement

The SELECT INTO statement produces a result table that contains at most one row. The statement assigns the values in that row to variables. If the table is empty, the statement does not assign values to the host variables or global variables.

Invocation for SELECT INTO

This statement can only be embedded in an application program. It is an executable statement that cannot be dynamically prepared.

Authorization for SELECT INTO

The privilege set that is defined below must include at least one of the following:
  • The SELECT privilege on every table and view identified in the statement
  • Ownership of every table and view identified in the statement
  • READ privileges on any global variables that are identified in the statement
  • Ownership of any global variables that are identified in the statement
  • 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.

Start of changeFor an assignment to a global variable or an element of an array global variable, the privilege set must include at least one of the following:End of change

Start of change
  • The WRITE privilege on the variable
  • Ownership of the variable
  • DATAACCESS authority
  • SYSADM authority
End of change

Start of changeFor an assignment to a transition variable, the privilege set must include at least one of the following:End of change

Start of change
  • The UPDATE privilege on the table or view on which the trigger that contains the assignment statement is defined
  • The UPDATE privilege on the column corresponding to the transition variable to be assigned a value
  • Ownership of the table or view on which the trigger that contains the assignment statement is defined
  • DBADM authority on the database that contains the table on which the trigger that contains the assignment statement is defined
  • DATAACCESS authority
  • SYSADM authority
End of change

Start of changePrivilege set: If the statement is embedded in an application program, the privilege set is the privileges that are held by the owner of the package. If the statement is dynamically prepared, the privilege set is determined by the DYNAMICRULES behavior in effect (run, bind, define, or invoke) and is summarized in DYNAMICRULES behaviors and authorization checking. For more information on these behaviors, including a list of the DYNAMICRULES bind option values that determine them, see Authorization IDs and dynamic SQL.End of change

Syntax for SELECT INTO

Read syntax diagramSkip visual syntax diagramWITH,common-table-expression select-clause1 INTO ,target-variablearray-variable[ array-index] from-clause where-clause group-by-clause having-clause order-by-clauseoffset-clausefetch-clause2isolation-clauseSKIP LOCKED DATAQUERYNOinteger
target-variable
Read syntax diagramSkip visual syntax diagramglobal-variable-namehost-variable-nameSQL-parameter-nameSQL-variable-nametransition-variable-name
Notes:
  • 1 The select-clause cannot reference both a system-period temporal table and an archive-enabled table.
  • 2 The same clause must not be specified more than once.

Description for SELECT INTO

Start of changeThe result table is derived by logically evaluating the isolation-clause, from-clause, where-clause, group-by-clause, having-clause, order-by-clause, offset-clause, fetch-clause, and the select-clause, in this order. The actual order might be slightly different based on the plan that is chosen by the Db2 subsystem. See the topics under Queries for a description of these clauses.End of change

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 target-variable or array-variable[array-index]
Identifies one or more targets for the assignment of output values. The number of targets in the INTO clause must equal the number of values that are to be assigned. 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. A target variable must not be specified more than once in the INTO clause. Each assignment to a target is made in sequence through the list, according to the rules described in Assignment and comparison.

The value 'W' is assigned to the SQLWARN3 field of the SQLCA if the number of targets is less than the number of result column values.

If an error occurs on any assignment, the value is not assigned to the target, and no more values are assigned to the specified targets. Any values that have already been assigned remain assigned.

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.
Start of changetransition-variable-nameEnd of change
Start of changeIdentifies the column that is to be updated in the transition table. 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.End of change
array-variable [array-index]
Specifies an array element that is the target of the assignment.

An array element must not be specified as the target for an assignment if common-table-expression is also specified in the statement.

array-variable
Specifies an array variable.
[array-index]
An expression that specifies which element in the array is the target of the assignment.

For an ordinary array, the array index expression must be castable to INTEGER, and must not be the null value. The index value must be between 1 and the maximum cardinality that is defined for the array.

For an associative array, the array index expression must be castable to the index data type of the associative array, and must not be the null value.

array-index must not be:

  • An expression that references the CURRENT DATE, CURRENT TIME, or CURRENT TIMESTAMP special register
  • A nondeterministic function
  • A function that is defined with EXTERNAL ACTION
  • A function that is defined with MODIFIES SQL DATA
  • A sequence expression
  • A column that is not in a scalar fullselect.

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 Assignment and comparison.

Each assignment to a variable is made according to the rules described in Assignment and comparison. 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 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 might be assigned to the variables. If values are assigned to the variables, the row that is the source of the values is undefined and not predictable.

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 more information about enabling and using optimization hints,, see Influencing access path selection.

Notes for SELECT INTO

Assignment to targets
The nth target identified by the INTO clause or described in the SQLDA corresponds to the nth column of the result table of the cursor. The data type of target must be compatible with its corresponding value. If the value is numeric, the target must have the capacity to represent the whole part of the value. For a datetime value, the target must be a character string variable of a minimum length as defined in String representations of datetime values. When the target is a host variable, if the value is null, an indicator variable must be specified.

Assignments are made in sequence through the list. Each assignment to a target is made according to the rules described in Language elements in SQL. If the number of targets is less than the number of values in the row, the SQLWARN3 field of the SQLCA is set to 'W'. There is no warning if there are more targets than the number of result columns. If the target is a host variable and the value is null, an indicator variable must be provided. If an assignment error occurs, the value is not assigned to the target and no more values are assigned to targets. Any values that have already been assigned to targets remain assigned.

If more than one assignment is included in the same assignment statement, all expressions are evaluated before the assignments are performed. For example, a reference to a variable in an expression always uses the value of the variable prior to any assignment in the assignment statement.

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 targets with other data types. For more information on using locators, see Saving storage when manipulating LOBs by using LOB locators.

A timestamp without time zone value must not be assigned to a timestamp with time zone target.

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 or global 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.
Start of changeoffset-clause considerationsEnd of change
Start of change
  • The offset-clause can be used in the SELECT INTO statement when the query can result in more than a single row.
  • To influence which row is returned, you can use the order-by-clause. When you specify the order-by-clause, the rows of the result are ordered, the specified number of rows are skipped, and the first row is returned. If the fetch-clause is not specified and the result table contains more than a single row, an error occurs.
End of change
Start of changefetch-clause considerationsEnd of change
Start of change
  • The fetch-clause can be used in a SELECT INTO statement when the query can result in more than a single row. Specifying FETCH FIRST 1 ROW ONLY indicates that at most one row can be retrieved, regardless of how many rows are in the result table.
  • Using the fetch-clause to explicitly limit the result table to a single row provides a way for a SELECT INTO statement to be used with a query that might return 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 the order-by-clause, the rows of the result are ordered and then the first row is returned. If the fetch-clause is not specified and the result table contains more than a single row, an error occurs.
End of change

Examples for SELECT INTO

Example 1
Put the maximum salary in DSN8C10.EMP into the host variable MAXSALRY. 
   EXEC SQL SELECT MAX(SALARY)
     INTO :MAXSALRY
     FROM DSN8C10.EMP;
Example 2
Put the row for employee 528671, from DSN8C10.EMP, into the host structure EMPREC. 
   EXEC SQL SELECT * INTO :EMPREC
     FROM DSN8C10.EMP
     WHERE EMPNO = '528671'
   END-EXEC.
Example 3
Put the row for employee 528671, from DSN8C10.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 DSN8C10.EMP
     WHERE EMPNO = '528671'
     WITH RS USE AND KEEP EXCLUSIVE LOCKS
   END-EXEC.
Example 4
Using a SELECT INTO statement, retrieve the value of INTCOL1 from table T1 into an element in array MYINTARRAY1, which is indexed by the value of the expression INTCOL2+MYINTVAR+1.
SELECT INTCOL1 INTO MYINTARRAY1[INTCOL2+MYINTVAR+1]
 FROM T1
 WHERE INTCOL1 = MYINTARRAY1[INTCOL2] ;