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.

Invocation

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

Authorization

The privileges held by the authorization ID of the statement must include at least one of the following:

• For each table or view identified in the statement,
  • The SELECT privilege on the table or view, and
  • The system authority *EXECUTE on the library containing the table or view
• Administrative authority

The authorization ID of the statement has the SELECT privilege on a table when:

• It is the owner of the table,
• It has been granted the SELECT privilege on the table, or
• It has been granted the system authorities of *OBJOPR and *READ on the table.

The authorization ID of the statement has the SELECT privilege on a view when:

• It is the owner of the view,
• It has been granted the SELECT privilege on the view, or
• It has been granted the system authorities of *OBJOPR and *READ on the view and the system authority *READ on all tables and views that this view is directly or indirectly dependent on. That is, all tables and views referenced in the view definition, and if a view is referenced, all tables and views referenced in its definition, and so forth.

Syntax

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

Description

The result table is derived by evaluating the from-clause, where-clause, group-by-clause, having-clause, select-clause, and order-by-clause, in this order.

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

Note that the grouping, as specified by the group-by-clause, strongly implies a result table of more than one row, and that a having-clause is probably needed to reduce the table to at most one row.

INTO host variable,...

Identifies one or more host structures or variables that must be declared 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 host structure is replaced by a reference to each of its variables. The first value in the result row is assigned to the first host variable in the list, the second value to the second host variable, and so on. The data type of each host variable must be compatible with its corresponding column.

Each assignment to a host variable is performed according to the rules described in "Language Elements". If the number of variables is less than the number of values in the row, the SQLWARN3 field of the SQLCA is set to 'W'. Note that there is no warning if there are more host variables than the number of result columns. If the value is null, an indicator variable must be provided. If an assignment error occurs, the value of that host variable and any following host variables is unpredictable. Any values that have already been assigned to variables remain assigned.

If any of the following data mapping errors occur, when evaluating a result column in the select-clause, the result is a null value:

• Characters could not be converted
• Numeric conversion error (underflow or overflow)
• Arithmetic expression error (division by 0)
• Date or timestamp conversion error (a date or timestamp that is not within the valid range of the dates for the specified format)
• String representation of the datetime value is not valid
• Mixed data not properly formed
• A numeric value is not valid
• Argument of SUBSTR scalar function is out of range

As in any other case of a null value, an indicator variable must be provided. The value of the host variable is undefined. 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. The value of the variable or any following variables is unpredictable. Any values that have already been assigned to variables remain assigned.

If an error occurs (SQLCODE equals -811) because the result table has more than one row, values are assigned to all host variables, but the row that is the source of the values is undefined and not predictable.

Examples

Example 1

Using a COBOL program statement, put the maximum salary (SALARY) from the EMPLOYEE table into the host variable MAX-SALARY (dec(9,2)) with isolation level Read Committed (CS).

   EXEC SQL  SELECT MAX(SALARY)
               INTO :MAX-SALARY
               FROM EMPLOYEE WITH CS
   END-EXEC.

Example 2

Using a PL/I program statement, select the row from the EMPLOYEE table with a employee number (EMPNO) value the same as that stored in the host variable HOST_EMP char(6)). Then put the last name (LASTNAME) and education level (EDLEVEL) from that row into the host variables HOST_NAME (char(20)) and HOST_EDUCATE (integer).

   EXEC SQL  SELECT LASTNAME, EDLEVEL
               INTO :HOST_NAME, :HOST_EDUCATE
               FROM EMPLOYEE
               WHERE EMPNO = :HOST_EMP;