Constant Expressions

If a column name is not specified in the target list of the derived-table expression, as in the case where a constant expression is used for the column name, the resulting column in the SQL-derived table has no name.

For example:

1> select * from
2> (select title_id, (lorange + hirange)/2 
3> from roysched) as dt_avg_range
4> go
title_id
--------- -----------
BU1032    2500
BU1032    27500
PC1035    1000
PC1035    2500

You can specify column names for the target list of a derived-table expression using a derived column list:

1> select * from
2> (select title_id, (lorange + hirange)/2 
3> from roysched) as dt_avg_range (title, avg_range)
4> go
title     avg_range
--------- -----------
BU1032    2500
BU1032    27500
PC1035    1000
PC1035    2500

Alternately, you can specify column names by renaming the column in the target list of the derived-table expression:

1> select * from
2> (select title_id, (lorange + hirange)/2 avg_range
3> from roysched) as dt_avg_range
4> go
title     avg_range
--------- -----------
BU1032    2500
BU1032    27500
PC1035    1000
PC1035    2500

Note: If you specify column names in both a derived column list and in the target list of the derived-table expression, the resulting columns are named by the derived column list. The column names in a derived column list take precedence over the names specified in the target list of the derived-table expression.

If you use a constant expression within a create view statement, you must specify a column name for the constant expression results.