The rules and restrictions are:
- SELECT must be performed from within a Selection DECLARE CURSOR construct.
- For updatable cursors opened for SELECT statements only, no ORDER BY, GROUP BY, HAVING, or WITH ... BY clause is allowed.
- The SELECT privilege is required on all tables specified in the FROM clause and in any subquery contained in the query specification, or on the database set containing the tables.
- The number of columns in the select list must match the number of host variables.
- The database assigns values to the host variables specified in the INTO clause in the order in which the host variables are specified. The system assigns values to the status parameters SQLSTATE and SQLCODE last.
- The main host variable and the corresponding column in the returned data set must be of the same data type group, except that if the main host variable has an approximate type, then the temporary table column data type can have either an approximate numeric or exact numeric type.
- If the temporary table is empty, the database assigns these values to the status parameters:
Status Parameter Assigned Value SQLSTATE ‘02000’ SQLCODE +100 Values are not assigned to the host variables specified in the INTO clause.
- If exactly one row of data is returned, the values from the row are assigned to the corresponding host variables specified in the INTO clause.
- If more than one row of data is returned, the system assigns these values to the status parameters:
Status Parameter Assigned Value SQLSTATE ‘21000’ SQLCODE -811 Values are not assigned to the host variables specified in the INTO clause.
- If an error occurs in assigning a value to a host variable, the system assigns one of these value sets to the status parameters:
Status Parameter Assigned Value SQLSTATE ‘22509’ SQLCODE -303 SQLSTATE ‘22003’ SQLCODE -304 SQLSTATE ‘22003’ SQLCODE -413 Values are not assigned to the host variables specified in the INTO clause.
- If a column value in the returned data is NULL and a corresponding indicator host variable is specified, the value -1 is assigned to the indicator host variable and no value is assigned to the main host variable.If no corresponding indicator host variable is specified, the database assigns these values to the status parameters:
Status Parameter Assigned Value SQLSTATE ‘22002’ SQLCODE -305 Values are not assigned to the host variables specified in the INTO clause.
- If a column value in the returned data is NOT NULL and a corresponding indicator host variable is specified, the indicator host variable is set as follows:
- If the column and main host variable are of CHARACTER data type and the column value is longer than the main host variable, the indicator host variable is set to the length of the column value.
- In all other cases, the indicator variable is set to zero.
Status parameters have the following default values:Status Parameter Assigned Value SQLSTATE ‘00000’ SQLCODE 0 - Column values are set in the corresponding main host variables according to the rules for host variables.
- SELECT …. INTO cannot be performed as a dynamic request.