Selects a single-row result table and assigns the values directly to host variables. Also known as a singleton
Values defined by the
WHEREclauses are assigned to target variables as specified in the
INTOclause. The value of the first element in the
SELECTclause 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
The result table defined by the
SELECT INTOstatement may not contain more than one row.
If a table reference or correlation name is used together with an asterisk in the
SELECTclause, all columns are selected from the referred table. Columns listed explicitly in the
SELECTclause 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
SELECTclause may be replaced by a single asterisk, in which case all columns from the table(s) or view(s) named in the
FROMclause 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
SELECTclause, their evaluation is influenced by the keywords
ALLis specified, all rows in the result table are used in calculating the result of the set function. If
DISTINCTis specified, duplicate rows are eliminated from the result table before the set function is evaluated. If no keyword is specified,
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.
SELECTaccess is required on all tables and views specified in the statement.
proceduralusage context, the
SELECT INTOstatement is only permitted if the routine
READS SQL DATAor
MODIFIES SQL DATA, see CREATE FUNCTION and CREATE PROCEDURE.
Correlation names used in the
WHEREclause must be defined in the
FROMclause of the same
SELECT INTOstatement. The same correlation name may not be defined more than once in one
SELECT INTOstatement may include a
HAVINGclause. However, care must be exercised to ensure that the
HAVINGclause 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.
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 1 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 1;
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40