SELECT INTO

Selects a single-row result table and assigns the values directly to host variables. Also known as a singleton SELECT.

 

Usage

Embedded, Procedural.

Description

Values defined by the SELECT, FROM and WHERE clauses are assigned to target variables as specified in the INTO clause. The value of the first element in the SELECT clause is assigned to the first variable, the value of the second element to the second variable, and so on. The data types of the variables must be assignment-compatible with those of the corresponding values.

The number of elements in the select-list must be the same as the number of elements in the target-variable list.

The result table defined by the SELECT INTO statement may not contain more than one row.

If a table reference or correlation name is used together with an asterisk in the SELECT clause, all columns are selected from the referred table. Columns listed explicitly in the SELECT clause need not be prefixed with the table or view name unless the same column name is used in more than one source table or view.

The whole list of values in the SELECT clause may be replaced by a single asterisk, in which case all columns from the table(s) or view(s) named in the FROM clause are selected, in the order in which they are defined in the source table(s) or view(s).

Note:Use of SELECT * is discouraged in are embedded in application programs (except in EXISTS predicates) since the asterisk is expanded to a column list when the statement is compiled, and any subsequent alterations in the table or view definitions may cause the program to function incorrectly.

When set functions are used in the list of values in the SELECT clause, their evaluation is influenced by the keywords ALL and DISTINCT. If ALL is specified, all rows in the result table are used in calculating the result of the set function. If DISTINCT is specified, duplicate rows are eliminated from the result table before the set function is evaluated. If no keyword is specified, ALL is assumed.

Language Elements

expression, see Expressions.

search-condition, see Search Conditions.

target-variable, see Target Variables.

order-by-clause, see The ORDER BY Clause.

result-offset-clause, see The RESULT OFFSET Clause.

fetch-first-clause, see The FETCH FIRST Clause.

Restrictions

SELECT access is required on all tables and views specified in the statement.

In a procedural usage context, the SELECT INTO statement is only permitted if the routine access-clause is READS SQL DATA or MODIFIES SQL DATA, see CREATE FUNCTION and CREATE PROCEDURE.

Notes

Correlation names used in the SELECT or WHERE clause must be defined in the FROM clause of the same SELECT INTO statement. The same correlation name may not be defined more than once in one FROM clause.

A SELECT INTO statement may include a GROUP BY or HAVING clause. However, care must be exercised to ensure that the HAVING clause selects one and only one group, and that the selected group either contains only one member or is reduced to a single row by a set function.

Examples

Use the SUM aggregate function to make sure exactly one row is returned:

SELECT SUM(quantity * "VALUE") INTO :hv

FROM mimer_store.items AS msi

JOIN mimer_store.order_items AS msoi ON msi.item_id = msoi.item_id

WHERE order_id = :inval;

 

Use FETCH FIRST 1 ROW ONLY to make sure exactly one row is returned:

SELECT quantity, msi.item_id INTO :hv1, :hv2

FROM mimer_store.items AS msi

JOIN mimer_store.order_items AS msoi ON msi.item_id = msoi.item_id

ORDER BY quantity

FETCH FIRST 1 ROW ONLY;

Standard Compliance