6.9.2 Decision rules for derived column names in query results

In query results, derived column names are determined according to the following rules.

Organization of this subsection

(1) In the case of a query expression or query expression body
(2) In the case of a query specification or subquery
(3) In the case of a derived table
(4) In the case of a table function derived table
(5) In the case of a collection derived table

(1) In the case of a query expression or query expression body

The derived column names will be the column names derived from the results of the query-primary that is specified first.

To Page Top

(2) In the case of a query specification or subquery

■ If the AS clause is not specified in the i-th selection expression

If the value expression specified in the i-th selection expression is a column specification

The i-th derived column name will be that column name.
If the value expression specified in the i-th selection expression is a subquery

The i-th derived column name will be the column name derived from the result of the subquery.
If the value expression specified in the i-th selection expression is a field reference

The field name specified in the field reference becomes the i-th derived column name.
Other than above:

No column name is set for the derived column.

■ If the AS clause is specified in the i-th selection expression

The i-th derived column name will be the column name in the AS clause specified in the i-th selection expression.

The following examples illustrate the decision rules for derived column names.

Example 1:

SELECT "C1","C2","C3" FROM "T1"

If you execute the SELECT statement above, the derived column names will be "C1", "C2", and "C3". The column order of the derived columns is this same order.

Example 2:

SELECT "C1","C2" AS "X2","C3" FROM "T1"

If you execute the SELECT statement above, the derived column names will be "C1", "X2", and "C3". The column order of the derived columns is this same order.

Example 3:

SELECT "C1",SUM("C2") AS "SUM-C2",AVG("C2") FROM "T1"
    WHERE "C3">=DATE'2011-09-03'
    GROUP BY "C1"

If you execute the SELECT statement above, the derived column names will be "C1", "SUM-C2", and "no column name". The column order of the derived columns is this same order.

To Page Top

(3) In the case of a derived table

■ If a derived column list is specified

The i-th derived column name will be the i-th column name in the derived column list.

■ If no derived column list is specified

If a table subquery is specified as a derived table

The derived column names will be the column names of the table derived by the subquery.

If names to be given to derived columns are not set, character strings in the EXPnnnn_NO_NAME format are used as the names of the derived columns. The nnnn portion is an unsigned integer in the range from 0001 to 4000. The integer nnnn is a sequence number that will be given in ascending order (0001, 0002, ...) to each column for which a derived column name to be given is not set.
If a table value constructor is specified as a derived table

A character string in the EXPnnnn_NO_NAME format will become a derived column name. The nnnn portion is an unsigned integer in the range from 0001 to 4000. The integer nnnn is a sequence number that will be given in ascending order (0001, 0002, ...) to each derived column.

To Page Top

(4) In the case of a table function derived table

If a table function column list is specified

The i-th derived column name will be the i-th column name specified in the table function column list.
If a derived column list is specified

The i-th derived column name will be the i-th column name specified in the derived column list.
If a table function column list or a derived column list is not specified

The derived column name will be the column name derived by means of the system-defined function specified in the table function derived table.

If a derived column name is not set, EXPnnnn_NO_NAME becomes the derived column name. nnnn is an unsigned integer from 0001 to 4000. For nnnn, 0001, 0002, ... are assumed in order from the beginning for columns that do not have a derived column name set.

To Page Top

(5) In the case of a collection derived table

■ If a derived column list is specified: The i-th derived column name will be the i-th column name specified in the derived column list.
■ If no derived column list is specified: The i-th derived column name will be the i-th column specification column name specified in the collection derived table.

To Page Top