INTO Clause

Use the INTO clause in SPL routines or programs to specify the program variables or host variables to receive data that the SELECT statement retrieves.

This syntax fragment is part of the SELECT statement.

INTO Clause

Notes:

¹ ESQL/C only
² HCL OneDB™ extension


Element	Description	Restrictions	Syntax
data_ structure	Structure that was declared as a host variable	Data types of elements must be able to store the values that are being selected	Language specific
indicator_ var	Program variable to receive a return code if corresponding output_var receives a NULL value	Optional; use an indicator variable if the possibility exists that the value of the corresponding output_var is NULL	Language specific
output_ var	Program or host variable to receive value of the corresponding select list item. Can be a collection variable	Order of receiving variables must match the order of corresponding items in the select list of Projection clause	Language specific

The INTO clause specifies one or more variables that receive the values that the query returns. If it returns multiple values, they are assigned to the list of variables in the order in which you specify the variables. If the SELECT statement stands alone (that is, it is not part of a DECLARE statement and does not use the INTO clause), it must be a singleton SELECT statement. A singleton SELECT statement returns only one row. The following example shows a singleton SELECT statement in :

EXEC SQL SELECT fname, lname, company
   INTO :p_fname, :p_lname, :p_coname
   FROM customer WHERE customer_num = 101;

In an SPL routine, if a SELECT returns more than one row, you must use the FOREACH statement to access the rows individually. The INTO clause of the SELECT statement holds the fetched values. For more information, see FOREACH.

The number of receiving variables must be equal to the number of items in the select list of the Projection clause. The data type of each receiving variable should be compatible with the data type of the corresponding column or expression in the select list. If the data type of the receiving variable does not match that of the selected item, the data type of the selected item is converted, if possible, to the data type of the variable. If the conversion is impossible, an error occurs, and a negative value is returned in the status variable, sqlca.sqlcode, or SQLCODE. In this case, the value in the program variable is unpredictable. In an ANSI-compliant database, if the number of variables that are listed in the INTO clause differs from the number of items in the select list of the Projection clause, you receive an error.

In , if the number of variables listed in the INTO clause differs from the number of items in the Projection clause, a warning is returned in the sqlwarn structure: sqlca.sqlwarn.sqlwarn3. The actual number of variables that are transferred is the lesser of the two numbers. For information about the sqlwarn structure, see the HCL OneDB ESQL/C Programmer's Manual.