Naming View Columns

The number of columns that you specify in the column list must match the number of columns returned by the SELECT statement that defines the view. If you do not specify a list of columns, the view inherits the column names of the underlying tables. In the following example, the view herostock has the same column names as the columns in Projection clause of the SELECT statement:

CREATE VIEW herostock AS
   SELECT stock_num, description, unit_price, unit, unit_descr
      FROM stock WHERE manu_code = 'HRO'

You must specify at least one column name in the following circumstances:

• If you provide names for some of the columns in a view, then you must provide names for all the columns. That is, the column list must contain an entry for every column that appears in the view.
• If the SELECT statement returns an expression, the corresponding column in the view is called a virtual column. You must provide a name for a virtual column. In the following example, the user must specify the column parameter because the select list of the Projection clause of the SELECT statement contains an aggregate expression:

  CREATE VIEW newview (firstcol, secondcol) AS
           SELECT sum(cola), colb FROM oldtab

• You must also specify column names in cases where any of the selected columns have duplicate column names without the table qualifiers. For example, if both orders.order_num and items.order_num appear in the SELECT statement, the CREATE VIEW statement, must provide two separate column names to label them:

  CREATE VIEW someorders (custnum,ocustnum,newprice) AS 
           SELECT orders.order_num,items.order_num,
                 items.total_price*1.5
              FROM orders, items
              WHERE orders.order_num = items.order_num
              AND items.total_price > 100.00

  Here custnum and ocustnum replace the two identical column names.

• The CREATE VIEW statement must also provide column names in the column list when the SELECT statement includes a UNION or UNION ALL operator and the names of the corresponding columns in the SELECT statements are not identical.

  For example, code in the following CREATE VIEW statement must specify the column list because the second column in the first SELECT statement has a different name from the second column in the second SELECT statement:

  CREATE VIEW myview (cola, colb) AS
           SELECT colx, coly from firsttab
           UNION
           SELECT colx, colz from secondtab