select-clause

.-ALL------.
>>-SELECT--+----------+----------------------------------------->
'-DISTINCT-'
>--+-*-------------------------------------------+-------------><
| .-,---------------------------------------. |
| V | |
'---+-expression--+---------------------+-+-+-'
| | .-AS-. | |
| '-+----+--column-name-' |
+-table-name.*------------------------+
+-view-name.*-------------------------+
'-correlation-name.*------------------'
The SELECT clause specifies the columns of the final
result table. The column values are produced by the application of the select list to R. The select list is the names or expressions
specified in the SELECT clause, and R is the result of the previous operation
of the subselect. For example, if the only clauses specified are SELECT,
FROM, and WHERE, R is the result of that WHERE clause.
- ALL
- Selects all rows of the final result table and does not eliminate duplicates.
This is the default.
- DISTINCT
- Eliminates all but one of each set of duplicate rows of the final result
table. Two rows are duplicates of one another only if each value in the first
row is equal to the corresponding value in the second row. (For determining
duplicate rows, two null values are considered equal.) Sort sequence is also
used for determining distinct values.
DISTINCT is not allowed if the select list contains a LOB or DATALINK column.
Select list notation
- *
- Represents a list of columns of table R in the order the columns are
produced by the FROM clause. The list of names is established when the statement
containing the SELECT clause is prepared. Therefore, * does not identify
any columns that have been added to a table after the statement has been
prepared.
- expression
- Specifies the values of a result column. Each column-name in
the expression must unambiguously identify a column of R.
- column-name or AS column-name
- Names or renames the result column. The name must not be qualified and
does not have to be unique.
- name.*
- Represents a list of columns of name in the order the columns
are produced by the FROM clause. The name can be a table name, view
name, or correlation name, and must designate an exposed table, view, or correlation
name in the FROM clause immediately following the SELECT clause. The first
name in the list identifies the first column of the table or view, the second
name in the list identifies the second column of the table or view, and so
on.
The list of names is established when the statement containing the
SELECT clause is prepared. Therefore, * does not identify any columns
that have been added to a table after the statement has been prepared.
Normally, when SQL statements are implicitly rebound, the list of names
is not re-established. Therefore, the number of columns returned by the statement
does not change. However, there are four cases where the list of names is
established again and the number of columns can change:
- When an SQL program or SQL package is saved and then restored on an iSeries system
that is not the same release as the system from which it was saved.
- When SQL naming is specified for an SQL program or package and the owner
of the program has changed since the SQL program or package was created.
- When an SQL statement is executed for the first time after the install
of a more recent release of i5/OS.
- When the SELECT * occurs in the fullselect of an INSERT statement
or in a fullselect within a predicate, and a table or view referenced in the
fullselect has been deleted and recreated with additional columns.
The number of columns in the result of SELECT is the same as the number
of expressions in the operational form of the select list (that is, the list
established at prepare time), and cannot exceed 8000. The result of a subquery
must be a single expression, unless the subquery is used in the EXISTS predicate.
Applying the select list
The results of applying the select list to R depend on whether or not GROUP
BY or HAVING is used:
If GROUP BY or HAVING is used
- Each column-name in the select list must identify a grouping
expression or be specified within an aggregate function:
- If the grouping expression is a column name, the select list may apply
additional operators to the column name. For example, if the grouping expression
is a column C1, the select list may contain C1+1.
- If the grouping expression is not a column name, the select list may not
apply additional operators to the expression. For example, if the grouping
expression is C1+1, the select list may contain C1+1, but not (C1+1)/8.
- The RRN, DATAPARTITIONNAME, DATAPARTITIONNUM, DBPARTITIONNAME, DBPARTITIONNUM,
and HASHED_VALUE functions cannot be specified in the select list.
- The select list is applied to each group of R, and the result
contains as many rows as there are groups in R. When the select list is applied
to a group of R, that group is the source of the arguments of the aggregate
functions in the select list.
If neither GROUP BY nor HAVING is used
- The select list must not include any aggregate functions, or
each column-name must be specified in an aggregate function or be
a correlated reference.
- If the select list does not include aggregate functions, it is applied
to each row of R and the result contains as many rows as there are rows in
R.
- If the select list is a list of aggregate functions, R is the source of
the arguments of the functions and the result of applying the select list
is one row.
In either case the nth column of the result contains
the values specified by applying the nth expression
in the operational form of the select list.
Null attributes of result columns
Result columns allow null values if they are derived from:
- Any aggregate function but COUNT and COUNT_BIG
- Any column that allows null values
- A scalar function or expression with an operand that allows null values
- A host variable that has an indicator variable, or in the case of Java(TM), a variable or expression whose type is able to represent a Java null value
- A result of a UNION or INTERSECT if at least one of the corresponding
items in the select list is nullable
- An arithmetic expression in the outer select list
- A scalar fullselect
- A user-defined scalar or table function
Names of result columns
- If the AS clause is specified, the name of the result column is the name
specified on the AS clause.
- If the AS clause is not specified and a column list is specified in the
correlation clause, the name of the result column is the corresponding name
in the correlation column list.
- If neither an AS clause nor a column list in the correlation clause is
specified and the result column is derived only from a single column (without
any functions or operators), then the result column name is the unqualified
name of that column.
- All other result columns are unnamed.
Data types of result columns
Each column of the result of SELECT acquires a data type from the expression
from which it is derived.
When the expression is: |
The data type of the result column is: |
the name of any numeric column |
the same as the data type of the column, with the same precision and
scale for decimal columns. |
an integer constant |
INTEGER or BIGINT (if the value of the constant is outside the range
of INTEGER, but within the range of BIGINT). |
a decimal or floating-point constant |
the same as the data type of the constant, with the same precision
and scale for decimal constants. |
the name of any numeric variable |
the same as the data type of the variable, with the same precision
and scale for decimal variables. If the data type of the variable is not identical
to an SQL data type (for example, DISPLAY SIGN LEADING SEPARATE in COBOL),
the result column is decimal. |
an expression |
the same as the data type of the result, with the same precision and
scale for decimal results as described under Expressions. |
any function |
the data type of the result of the function. For a built-in function,
see Built-in functions to determine the data type of the
result. For a user-defined function, the data type of the result is what was
defined in the CREATE FUNCTION statement for the function. |
the name of any string column |
the same as the data type of the column, with the same length attribute. |
the name of any string variable |
the same as the data type of the variable, with a length attribute
equal to the length of the variable. If the data type of the variable is not
identical to an SQL data type (for example, a NUL-terminated string in C),
the result column is a varying-length string. |
a character-string constant of length n |
VARCHAR(n) |
a graphic-string constant of length n |
VARGRAPHIC(n) |
the name of a datetime column, or an ILE RPG compiler or ILE COBOL compiler datetime host
variable |
the same as the data type of the column or variable. |
the name of a datalink column |
a datalink, with the same length attribute. |
the name of a row ID column or a row ID variable |
ROWID |
the name of a distinct type column |
the same as the distinct type of the column, with the same length,
precision, and scale attributes, if any. |
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.