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
(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

(1) In the case of a query expression

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.
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. In this format, nnnn is an unsigned integer in the range from 0001 to 1000. 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. In this format, nnnn is an unsigned integer in the range from 0001 to 1000. 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 table function 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.

To Page Top