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 ESQL 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.

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.

Example

 SELECT SUM(quantity * "VALUE")
    INTO :hv
    FROM mimer_store.items
    NATURAL JOIN mimer_store.order_items
    WHERE order_id = data.order_id;

Standard Compliance

Standard	Compliance	Comments
SQL-2003	Core	Fully compliant.

Mimer Information Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 info@mimer.se