Selected Column (select_column)

Selected columns (select_column) must be specified in a QUERY specification in order to specify a results table.

The sequence of selected columns defines the columns in the results table. The columns in the results table are formed from the columns of the temporary results table, supplemented if necessary by the ROWNO columns (rowno_column) or STAMP columns (stamp_column). The columns of the temporary result table are determined by the FROM clause of the table expression. The order of the column names in the temporary results table is determined by the order of the table names in the FROM clause.

Structure

Syntax Syntax

<select_column> ::=
  <table_columns>
| <derived_column>
| <rowno_column>
| <stamp_column>

<table_columns> ::=
  *
| <table_name>.*
| <reference_name>.*

<derived_column> ::=
  <expression> [ [AS]<result_column_name>]

<rowno_column> ::=
  ROWNO [ [AS] <result_column_name>]

<stamp_column> ::=
  STAMP [ [AS] <result_column_name>]

<result_column_name> ::=
  <identifier>

End of the code.

Examples

SQL Tutorial, Selecting and Arranging Rows, Selecting, Arranging and Renaming Columns

Explanation

Every column name that is specified as a selected column (select_column) must uniquely denote a column in a QUERY specification of the underlying tables. If necessary, the column name must be qualified with the table name.

The specification of a column with the data type LOB in a selected column is only valid in the uppermost sequence of selected columns in a QUERY statement or SINGLE SELECT statement if the DISTINCT specification was not used there.

The specification of a column with the data type LOB in a selected column is only valid in the uppermost sequence of select columns in a CREATE VIEW statement which is based on exactly one base table.

If a selected column contains a set function, the sequence of selected columns to which the selected column belongs must not contain any table_columns, and every column name occurring in an expression must denote a grouping column, or the expression must consist of grouping columns.

It is possible to specify scalar subqueries.

<table_columns>

Specifying table columns (table_columns) in a selected column is a quick way of specifying the results table columns.

Specifying a selected column of the type * is a quick way of specifying all temporary results table columns. Columns for which the user does not have the SELECT privilege and the implicitly generated column SYSKEY are not transferred.
Specifying <table_name>.* or <reference_name>.* is a quick way of specifying all columns in the underlying table. The first column name of the result table is taken from the first column name of the underlying table, the second column name of the result table corresponds to the second column name of the underlying table, and so on. The order of column names in the underlying table corresponds to the order determined when the underlying table is defined. Columns for which the user does not have the SELECT privilege and the implicitly generated column SYSKEY are not transferred.

<derived_column>

Specifying the results table column derived_column in a selected column defines a column in the results table.

If a column of the result table has the form <expression> [AS] <result_column_name>, this result column receives the name result_column_name.
If no result_column_name is specified and the expression is a column specification that denotes a column in the temporary result table, the column in the result table receives the column name of the temporary result table.
If no result_column_name is specified and the expression is not a column specification, the column receives the name EXPRESSION_, where "_" denotes a number with a maximum of four digits, starting with EXPRESSION1, EXPRESSION2 and so on.

<rowno_column>

A ROWNO column (rowno_column) may only be used in a selected column that belongs to a QUERY statement.

If a ROWNO column is specified, a column with the data type FIXED(10) is generated with the name ROWNO. It contains the values 1, 2, 3, ... which are the numbers of the rows in the results table.

If the ROWNO column was specified in the form ROWNO [AS] <result_column_name>, this result column receives the name result_column_name.

A ROWNO column must not be ordered using ORDER BY.

<stamp_column>

A STAMP column (stamp_column) may only be specified in a selected column that belongs to the QUERY expression (query_expression) of an INSERT statement.

The database system is capable of generating unique values. This is a consecutive number that starts with X'000000000001'. The values are assigned in ascending order. There is no guarantee that a sequence of values is uninterrupted.

If a STAMP column is specified, the next value of the data type CHAR(8) BYTE generated by the database system is produced for each row in the temporary result table.

Further Explanations

Each column of a result table has exactly the same data type, length, precision and number of decimal places as the derived_column or the column underlying the table_columns.

This does not apply to the data types DATE and TIMESTAMP. To enable the representation of any date and time format, the length of the result table column is set to the maximum length required for the representation of a date value (length 10) or a time stamp value (length 26).