Specifies a result table. A result table is an intermediate table
of values derived from columns and rows of one or more tables or
views that meet conditions specified by a select expression.
The tables or views that the columns and rows come from are
identified in the FROM clause of the statement.
The basic element of a SELECT statement is called a select
expression. The Select_Expressions HELP topic describes select
expressions in detail.
To retrieve rows of a result table in host language programs,
you must use the DECLARE CURSOR statement or a special form of
SELECT statement called a singleton select. A singleton select
statement specifies a one-row result table, and is allowed in
either precompiled programs or as part of a procedure in an SQL
module. A singleton select includes an additional clause, INTO,
to assign the values in the row to host language variables in a
program.
For information on the general form of the SELECT statement, see
the SELECT General_Form statement.
1 – Environment
You can use a singleton select statement:
o In interactive SQL
o Embedded in host language programs to be precompiled
o As part of a procedure in an SQL module
o In dynamic SQL as a statement to be dynamically executed
2 – Format
(B)0[m[1msingleton-select = [m
[1m [m
[1;4mSELECT[m[1m qwqqqqqqqqqqqqqwq> select-list qqk [m
[1m tq> [1;4mALL[m[1m qqqqqqu x [m
[1m mq> [1;4mDISTINCT[m[1m qj x [m
[1m lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m
[1m mq> into-target qqqqqqqqqqqqqqqqqk [m
[1m lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m
[1m m> [1;4mFROM[m[1m qwqq> table-ref qqqqqwqk [m
[1m mqqqqqqqqqq , <qqqqqj x [m
[1m lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m
[1m m> [1;4mWHERE[m[1m predicate qwqqqqqqqqqqqqq>qqqqqqqqqqqqqqqqqwk [m
[1m mq> [1;4mGROUP[m[1m [1;4mBY[m[1m qw> <column-name> wjx [m
[1m mqqqqqqq , <qqqqqj x [m
[1m lqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqj [m
[1m mwqqqqqqqqqqqqqqqqqqqqwqwqqqqqqqqqqqqqqqqqqqqqqqwqqqqk [m
[1m m> [1;4mHAVING[m[1m predicate qj mq> limit-to-clause qqqqj x [m
[1m lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m
[1mmqwqqqqqqqqqqqqqqqqqqqqqwqwqqqqqqqqqqqqqqqqqqqqqwqqqqqq>[m
[1m m> for-update-clause qj mq> optimize-clause qqj[m
(B)0[m[1minto-target =[m
[1mqqqq> [1;4mINTO[m[1m qqwqwq> <parameter> qqqqqqqqqqqwwq>[m
[1m x tq> <qualified-parameter> qux [m
[1m x mq> <variable> qqqqqqqqqqqqjx [m
[1m mqqqqqqqqqqqqq , <qqqqqqqqqqqqj [m
[1m [m
(B)0[m[1mfor-update-clause = [m
[1m [m
[1mqq> [1;4mFOR[m[1m [1;4mUPDATE[m[1m qwqqqqqqqqqqqqqqqqqqqqqqqqqwq> [m
[1m mqwq> OF <column-name> qwqj [m
[1m mqqqqqqqq , <qqqqqqqqqj [m
[1m [m
(B)0[m[1moptimize-clause = [m [1m [m [1m [m
[1m [m [1m [m [1m [m
[1mqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqq> [m
[1m mq> [1;4mOPTIMIZE[m[1m qqwqwq> [1;4mFOR[m[1m qwq> [1;4mFAST[m[1m [1;4mFIRST[m[1m qqqqqqqqwqqqqqqqqqqwqwqj [m
[1m x x tq> [1;4mTOTAL[m[1m [1;4mTIME[m[1m qqqqqqqqu[m [1m [m [1mx x [m
[1mx[m [1mx[m [1mmq> [1;4mSEQUENTIAL[m [1;4mACCESS[m [1mqj[m [1mx[m [1mx[m
[1m x tq> [1;4mUSING[m[1m <outline-name> qqqqqqqqqqqqqqqqqqu x [m
[1mx[m [1mtq> [1;4mWITH[m[1m qwq> [1;4mDEFAULT[m [1mqqwq> [1;4mSELECTIVITY[m [1mqu[m [1mx[m
[1mx[m [1mx[m [1mtq>[m [1;4mSAMPLED[m [1mqqu[m [1mx[m [1mx[m
[1mx[m [1mx[m [1mmq>[m [1;4mAGGRESSIVE[m[1m j[m [1mx[m [1mx[m
[1m x mq> [1;4mAS[m[1m <query-name> qqqqqqqqqqqqqqqqqqqqqqqj x [m
[1m mqqqqqqqqqqqqqqqq <qqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m
[1m [m [1m [m [1m [m
3 – Arguments
3.1 – INTO
Syntax options:
INTO parameter
INTO qualified-parameter
INTO variable
Specifies a list of parameters, qualified parameters
(structures), or variables to receive values from the columns
of the one-row result table. The variables named must have been
declared in the host program. If a variable named in the list
is a host structure, SQL considers the reference the same as a
reference to each of the elements of the host structure.
If the number of variables specified, either explicitly or by
reference to a host structure, does not match the number of
values in the row of the result table, SQL generates an error
when it precompiles the program or compiles the SQL module file.
If columns in the result table from a singleton select include
null values, the corresponding parameters must include indicator
parameters.
3.2 – select-list
For a description of select lists, see the Select_Expressions
HELP topic.