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:
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
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.
