SELECT INTO is an executable command that can only be embedded in a host language. SELECT cannot be dynamically prepared. Tables or views identified in the SELECT INTO statement can exist at the current location or any other location existing under (or accessible from) the XDB Server.
In order to select data from a table or view users must possess either SELECT or EXECUTE authority on the table or view accessed, DBADM authority on the database, SYSADM or SYSCTRL authority for the location, or be the owner of the table or view.
SELECT projection-list INTO host-var-list
FROM table-view-list
[WHERE search-condition]
[group-by-clause]
[having-clause]
[order-by-clause]
[WITH {RR [lock-clause] | RS [lock-clause] | CS | UR}]
[QUERYNO integer]
[FETCH FIRST [1] {ROW | ROWS} ONLY]
| projection-list | Preceded by the keyword SELECT, lists one or more columns and/or expressions. |
| host-var-list | Preceded by the keyword INTO, lists one or more host variables (separated by commas) that have been described in the host program according to the rules for describing host variables. See the examples for additional language specific information. |
| table-view-list | Preceded by the keyword FROM (see FROM Clause Syntax and Description), lists one or more tables or views separated by commas. |
| search-condition | Preceded by the keyword WHERE (see WHERE Clause Syntax and Description), describes one or more row selection criteria. Multiple selection conditions can be specified together using the AND and OR logical operators. |
A SELECT INTO command can retrieve at most one row from a table or view. If the selected table is empty, the SQLCODE indicator field in the SQLCA is assigned a value of +100. If the statement retrieves more than one row of data an error condition results (assigning SQLCODE a value of -811).
The SELECT INTO command statement differs from other types of SELECT commands in that retrieved row values are assigned directly to host variables specified in the INTO clause. Retrieved table values are derived by first evaluating the FROM clause, followed by the WHERE and SELECT clauses.
The INTO clause cannot appear in nested SELECT commands. ORDER BY and UNION are not allowed in SELECT commands containing an INTO clause. When a SELECT command includes an INTO clause, GROUP BY and HAVING are allowed only in nested select statements.
If no rows are retrieved, +100 is placed in SQLCODE, and the host variables are unchanged. If an error occurs when evaluating any column in the projection list a null value is placed in the host variable. To retrieve multiple rows of table values using an embedded SELECT statement, use a DECLARE CURSOR command statement along with the FETCH command.
Specifies an isolation level with which the statement is executed. You can override the isolation level of the statement using the WITH {CS | RS | RR | UR} clause. You can only specify the isolation levels that specifically apply to a statement. WITH UR can be specified only after SELECT and only if the result table is read-only. The WITH clause overrides the isolation level only for the statement in which it appears.
When you specify WITH RR KEEP UPDATE LOCKS, XDB Server acquires an exclusive (X) lock, instead of a shared (S) lock, on all the pages or rows that fall within the range of the selection expression. All the X locks are retained until the next commit point.
See the Server Administration Guide for information about locking and isolation levels.
Specifies the number to be used for this SQL statement in EXPLAIN output and trace records. The number is used for the QUERYNO columns of the plan tables for the rows that contain information about this SQL statement.
If the clause is omitted, the number associated with the SQL statement is the statement number assigned during precompilation. Thus, if the application program is changed and then precompiled, that statement number might change.
Using the QUERYNO clause to assign unique numbers to the SQL statements in a program is helpful for simplifying the use of optimization hints for access path selection, if hints are used.
The hostvarlist parameter is a list of host variables, the total of which are equal to the number of columns in projection-list.
Here is an example of a SELECT INTO statement. The record returned will be read into the host variables CNO and NAME (like FETCH):
EXEC SQL
SELECT cno, name
INTO :CNO, :NAME
FROM cust
WHERE name = :incno
END-EXEC