SELECT INTO

Start of changeThe 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.End of change

Invocation

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

Authorization

Start of changeThe 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)
End of change

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, 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 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

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-clause2isolation-clauseSKIP LOCKED DATAQUERYNOintegerFETCH FIRST1ROWROWSONLY
target-variable
Read syntax diagramSkip visual syntax diagramglobal-variable-namehost-variable-nameSQL-parameter-nameSQL-variable-name
Read syntax diagramSkip visual syntax diagram
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

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.
Start of changeINTO target-variable or array-variable[array-index]End of change
Start of changeIdentifies 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.
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
  • Start of changeA column that is not in a scalar fullselect.End of change

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.

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

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

Start of changeAssignment to targets:End of change
Start of changeThe 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.

Start of changeAssignments are made in sequence through the list. Each assignment to a target is made according to the rules described in Language elements. 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.End of change

Start of changeIf 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.End of change

Start of changeNormally, 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.End of change

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

End of change
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:
Start of changeIf 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.End of change
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.

Examples

Example 1: Put the maximum salary in DSN8B10.EMP into the host variable MAXSALRY.
   EXEC SQL SELECT MAX(SALARY)
     INTO :MAXSALRY
     FROM DSN8B10.EMP;
Example 2: Put the row for employee 528671, from DSN8B10.EMP, into the host structure EMPREC.
   EXEC SQL SELECT * INTO :EMPREC
     FROM DSN8B10.EMP
     WHERE EMPNO = '528671'
   END-EXEC.
Example 3: Put the row for employee 528671, from DSN8B10.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 DSN8B10.EMP
     WHERE EMPNO = '528671'
     WITH RS USE AND KEEP EXCLUSIVE LOCKS
   END-EXEC.
Start of changeExample 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.Start of change
SELECT INTCOL1 INTO MYINTARRAY1[INTCOL2+MYINTVAR+1]
 FROM T1
 WHERE INTCOL1 = MYINTARRAY1[INTCOL2] ;
End of changeEnd of change