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 and by the rowno columns (rowno_column) or stamp columns (stamp_column)., if these exist. The columns of the temporary results 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.

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>

Examples

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

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 LONG 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 LONG 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 (table_columns), and every column name occurring in an expression (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 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 has not the SELECT privilege and the implicitly generated column SYSKEY are not passed.

·        Specifying <table_name>.* or <reference_name>.* is quick way of specifying all the 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, etc. 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 has not the SELECT privilege and the implicitly generated column SYSKEY are not passed.

<derived_column>

Specifying 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 created 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 by 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'. Values are assigned in ascending order. There is no guarantee that the sequence of values will be 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 results table.

Additional Information

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 timestamp value (length 26).