SELECT
selection table-expression
|
A select-expression
specifies a result table. It is
used in the following Natural SQL statements: INSERT
| SELECT
| UPDATE
This document covers the following topics:
, | ||||||||
* |
A selection
specifies the columns of the result
set tables to be selected.
Syntax Element Description:
Syntax Element | Description |
---|---|
ALL|DISTINCT |
Elimination of Duplicate Rows:
Duplicate rows are not automatically eliminated from the result of a
The alternative to |
scalar-expression |
Scalar Expression:
Instead of, or as well as, simple column names, a selection can also include general scalar expressions containing scalar operators and scalar functions which provide computed values (see also the section Scalar Expressions). Example: SELECT NAME, 65 - AGE FROM SQL-PERSONNEL ... |
AS |
The optional keyword AS introduces a correlation-name for a
column.
|
correlation-name |
Correlation Name:
A The |
unpack-row |
See unpack-row below. |
* |
Asterisk Notation:
All columns of the result table are selected. Example: SELECT * FROM SQL-PERSONNEL, SQL-AUTOMOBILES ... |
UNPACK (scalar-expression) .* AS ({field-name
data-type} , )
|
An unpack-row
specifies a row of unpacked
binary values that are returned when the SQL UNPACK function is invoked. The number of
field-names and data-types must
match the number of fields returned by the UNPACK function.
from-clause [where-clause] |
[group-by-clause] [having-clause] |
[order-by-clause] [fetch-first-clause] |
The table-expression
specifies from where and
according to what criteria rows are to be selected.
The following topics are covered below:
FROM
table-reference,…
|
This clause specifies from which tables the result set is built.
The tables specified in the FROM
clause must contain the column fields
used in the selection list.
You can either specify a single table or produce an intermediate table resulting from a subquery or a "join" operation (see below).
Since various tables (that is, DDMs) can be addressed in one FROM
clause
and since a table-expression
can contain several
FROM
clauses if subqueries
are
specified, the database ID (DBID) of the first DDM specified in the first
FROM
clause of the whole expression is used to identify the underlying
database involved.
The TABLE function-name
clause belongs to the
SQL Extended Set and
requires a correlation-clause
with a
column-name
list.
The period-specification
clause belongs to
the SQL Extended Set.
FOR
|
period-specification
optionally specifies
that a period specification applies to the temporal table
table-name
. The same period name
(SYSTEM_TIME
or BUSINESS_TIME
) must not be specified more
than one time for the same table.
Optionally, a correlation-clause
can be
assigned to a table-name
. For a
subquery
, a
correlation-clause
must be
assigned.
[AS ] correlation-name [(column-name,...)]
|
A correlation-clause
consists of optional
keyword AS
and a correlation-name
and is
optionally followed by a plain column-name
list. The
column-name
list belongs to the SQL Extended Set.
table-reference |
|
JOIN
table-reference
ON
join-condition |
||||||
(joined-table) |
A joined-table
specifies an intermediate
table resulting from a "join" operation.
The "join" can be an INNER
, LEFT OUTER
,
RIGHT OUTER
or FULL OUTER
JOIN
. If you do not specify anything, INNER
applies.
Multiple "join" operations can be nested; that is, the tables which
create the intermediate result table can themselves be intermediate result tables of a
"join" operation or a subquery
;
and the latter, in turn, can also have a
joined-table
or another
subquery
in its FROM
clause.
For INNER
, LEFT OUTER
, and RIGHT OUTER
joins:
search-condition |
For FULL OUTER
joins:
full-join-expression =
full-join-expression [AND
]
|
column-name | |||||
|
(column-name
,
)
|
Within a join-expression
only
column-names
and the
scalar-function
VALUE
(or its synonym COALESCE
) are allowed.
See details on column-name
.
The data-change-table-reference
clause
belongs to the SQL Extended
Set.
FINAL TABLE (INSERT-statement)
|
|||||
TABLE (searched-UPDATE-statement)
|
|||||
OLD TABLE (searched-DELETE-statement)
|
|||||
FINAL TABLE (MERGE-statement)
|
A data-change-table-reference
specifies an
intermediate result table, which is based on the rows that are changed by the SQL
change statement specified in the clause. A
data-change-table-reference
can only be
specified as the only table reference in the FROM
clause.
Syntax Element Description:
The xmltable-function
clause belongs to the
SQL Extended Set.
The item xmltable-function
specifies an
invocation of the built-in XMLTABLE
function.
[WHERE
search-condition]
|
The WHERE
clause is used to specify the selection criteria
(search-condition
) for the rows to be
selected.
Example:
DEFINE DATA LOCAL 01 NAME (A20) 01 AGE (I2) END-DEFINE ... SELECT * INTO NAME, AGE FROM SQL-PERSONNEL WHERE AGE = 32 END-SELECT ...
For further information, see Search Conditions.
GROUP BY |
, |
The GROUP BY
clause specifies a grouping of the result table. The result
of GROUP BY
is a set of groups of rows. Within each group of more than one
row, all values defining the group are equal.
- grouping-expression
A grouping expression is a scalar expression that defines the grouping of a result set.
- grouping-set
GROUPING SETS
( , ) ( , ) A
grouping-set
is used to specify multiple grouping clauses in a single statement. Agrouping-set
combines two or more groups of rows into a single result set. It is the same as the union of multiple select expressions with aGROUP BY
clause where each expression corresponds to onegrouping-set
. Agrouping-set
is a single element or a list of elements delimited by parentheses. An element is either agrouping–expression
or asuper-group
. Agrouping-set
has the advantage that the groups are computed with a single pass over the base table.- super-group
(grouping-expression-list)
(grouping-expression-list( )
A
super-group
is a more complexgrouping-set
.A
grouping-expression-list
defines the number of elements used in aROLLUP
orCUBE
operation. Elements with multiplegrouping-expressions
are delimited by parentheses:
grouping-expression , (grouping-expression, )
ROLLUP
andCUBE
return a row which is the overall (grand total) aggregation. This can be specified with empty parentheses( )
within theGROUPING SETS
clause.
- ROLLUP
A
ROLLUP
grouping is like a series ofgrouping-sets
. In addition to the regular grouped rows, aROLLUP
grouping produces a result set that contains subtotal rows. Subtotal rows are "super-aggregate" rows which contain additional aggregates. The aggregate values are retrieved with the same column functions that are used to obtain the regular grouped rows.In general, you specify a
ROLLUP
withn
elements asGROUP BY ROLLUP (c1, c2, ..., cn-1, cn)which is the equivalent of:
GROUP BY GROUPING SETS ((c1, c2, ..., cn-1, cn), (c1, c2, ..., cn-1), ... (c1, c2), (c1), ( ))- CUBE
A
CUBE
grouping is like a series ofgrouping-sets
. In addition to theROLLUP
aggregation rows,CUBE
produces a result set that contains cross-tabulation rows. Cross-tabulation rows are additional "super-aggregate" rows. Thegrouping-expression-list
of aCUBE
computes all permutations along with the grand total. As a result, then
elements of aCUBE
translate to 2**ngrouping-sets
. For example:GROUP BY CUBE (a, b, c)is the equivalent of:
GROUP BY GROUPING SETS ((a, b, c), (a, b), (a, c), (b, c), (a), (b), (c), ())
[HAVING
search-condition]
|
If the HAVING
clause is specified, the GROUP BY
clause should
also be specified.
Just as the WHERE
clause is used to exclude rows from a result table, the
HAVING
clause is used to exclude groups and therefore also based on a
search-condition
. Scalar expressions in a
HAVING
clause must be single-valued per group.
For further information, see Scalar Expressions and Search Conditions.
Example:
DEFINE DATA LOCAL 1 #NAME (A20) 1 #AVGAGE (I2) 1 #NUMBER (I2) END-DEFINE ... SELECT NAME, AVG(AGE), COUNT(*) INTO #NAME, #AVGAGE, #NUMBER FROM SQL-PERSONNEL GROUP BY NAME HAVING COUNT(*) > 1 ...
ORDER BY
|
sort-key |
|
, | ||||
INPUT
SEQUENCE |
|||||||
ORDER OF
table-designator |
The order-by
clause specifies row
ordering of the result table.
column-name | ||
integer | ||
sort-key-expression |
A sort-key-expression
is an expression which
is more than a column-name
or an unsigned
integer
constant.
INPUT SEQUENCE
belongs to the SQL Extended Set.
INPUT SEQUENCE
indicates that the result table reflects the input order of
the rows specified in the VALUES
clause of an INSERT
statement.
INPUT SEQUENCE
can only be specified if an INSERT
statement
is specified in the from-clause
.
ORDER OF table-designator
belongs to the
SQL Extended Set.
ORDER OF
specifies that the row ordering of the designated table by the
table-designator
is applied to the result
table of the query.
table-designator
uniquely identifies a base
table, a view, or the nested table expression of a subselect.
The fetch-first
clause belongs to the
SQL Extended Set.
FETCH FIRST
|
1 integer |
ROWS ROW |
ONLY
|
The fetch-first
clause limits the
number of rows that can be fetched. It improves the performance of queries when only a
limited number of rows are needed.
Example 1:
DEFINE DATA LOCAL 01 #NAME (A20) 01 #FIRSTNAME (A15) 01 #AGE (I2) ... END-DEFINE ... SELECT NAME, FIRSTNAME, AGE INTO #NAME, #FIRSTNAME, #AGE FROM SQL-PERSONNEL WHERE NAME IS NOT NULL AND AGE > 20 ... DISPLAY #NAME #FIRSTNAME #AGE END-SELECT ... END
Example 2:
DEFINE DATA LOCAL 01 #COUNT (I4) ... END-DEFINE ... SELECT SINGLE COUNT(*) INTO #COUNT FROM SQL-PERSONNEL ...