SQL Reference

SELECT INTO

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

The privileges held by the authorization ID of the statement must include, for each table or view referenced in the SELECT INTO statement, at least one of the following:

• SELECT privilege
• CONTROL privilege
• SYSADM or DBADM authority.

GROUP privileges are not checked for static SELECT INTO statements.

Syntax

                          .-,----------------.
                          V                  |
>>-select-clause--INTO-------host-variable---+--from-clause----->
 
>----+--------------+--+-----------------+--+---------------+--><
     '-where-clause-'  '-group-by-clause-'  '-having-clause-'
 

Description

See Queries for a description of the select-clause, from-clause, where-clause, group-by-clause, and having-clause.

INTO

Introduces a list of host variables.

host-variable

Identifies a variable that is described in the program under the rules for declaring 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 Appendix B, SQL Communications (SQLCA).)

Each assignment to a variable is made according to the rules described in Assignments and Comparisons. Assignments are made in sequence through the list.

If an error occurs, no value is assigned to any host variable.

Examples

Example 1:  This C example puts the maximum salary in EMP 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 EMP, into host variables.

   EXEC SQL SELECT * INTO :h1, :h2, :h3, :h4
     FROM EMP
     WHERE EMPNO = '528671';