Static SELECT statements
For a static SELECT statement (one embedded in an SQL program), an INTO clause must be specified before the FROM clause.
The INTO clause names the host variables (variables in your program used to contain retrieved column values). The value of the first result column specified in the SELECT clause is put into the first host variable named in the INTO clause; the second value is put into the second host variable, and so on.
The result table for a SELECT INTO should contain just one row. For example, each row in the CORPDATA.EMPLOYEE table has a unique EMPNO (employee number) column. The result of a SELECT INTO statement for this table, if the WHERE clause contains an equal comparison on the EMPNO column, will be exactly one row (or no rows). Finding more than one row is an error, but one row is still returned. You can control which row will be returned in this error condition by specifying the ORDER BY clause. If you use the ORDER BY clause, the first row in the result table is returned.
If you want more than one row to be the result of a SELECT INTO statement, use a DECLARE CURSOR statement to select the rows, followed by a FETCH statement to move the column values into host variables one or many rows at a time.
When using the select-statement in an application program, list the column names to give your program more data independence. There are two reasons for this:
- When you look at the source code statement, you can easily see the one-to-one correspondence between the column names in the SELECT clause and the host variables named in the INTO clause.
- If a column is added to a table or view you access and you use "SELECT * ...," and you create the program again from source, the INTO clause does not have a matching host variable named for the new column. The extra column causes you to get a warning (not an error) in the SQLCA (SQLWARN3 will contain a "W"). When using the GET DIAGNOSTICS statement, the RETURNED_SQLSTATE item will have a value of '01503'.
Parent topic:
Retrieving data using the SELECT statement
Related reference
Using a cursor