Naming View Columns

An issue to consider in how views work is view column names. Assigning alias names to a view's columns is required when either of these rules is fulfilled:

• One or more of the view's columns is a complex expression: It includes an arithmetic expression, a built-in function, or a constant.

• The view would wind up with more than one column of the same name (the view definition's SELECT statement includes a join, and the columns from the joined tables or views have the same name).

You can assign names to view columns or expressions in two ways:

• Put the names inside parentheses following the view name, separated by commas. Make sure there is a name for every item in the SELECT list. If you do one column name this way, you must do them all the same way.

• Allow the view to inherit names from the columns or display labels in the SELECT clause. Display labels are required for complex expressions and columns with name conflicts. Otherwise, you can leave column names as they are.

Figure 9.3 shows both methods.

Complex Expressions

Assigning column names in the CREATE VIEW clause can be illustrated with the currentinfo view discussed earlier in this chapter:

create view currentinfo (PUB#, TYPE, INCOME,
							  AVG_PRICE, AVG_SALES)
as
select pub_id, type, sum(price*ytd_sales),
							  avg(price), avg(ytd_sales)
from titles
group by pub_id, type

The computed columns in the SELECT list don't really have names, so you must give them new names in the CREATE VIEW clause or assign display labels in the SELECT clause. Otherwise, you'd have no way to refer to them. When you work with the view currentinfo, always use the new names, like this:

select PUB#, AVG_SALES
from currentinfo

Using the old names, such as pub_id or avg(ytd_sales), won't work.

Duplicate Column Names

The second circumstance in which assigning new column names is required usually arises when there's a join in the SELECT statement and the joining columns have the same name. Even though they are qualified with different table names in the SELECT statement, you have to rename them in order to resolve the ambiguity:

create view cities (Author, Authorcity, Pub, Pubcity)
as
select au_lname, authors.city, pub_name, publishers.city
from authors, publishers
where authors.city = publishers.city

Of course, you are free to rename columns in a view definition statement whenever it's helpful to do so. Just remember that when you assign column names in the CREATE VIEW clause, the number and order of column names inside the parentheses has to match the number and order of items in the SELECT list.

Whether you rename a view column, its datatype and null status depend on how it was defined in its base table(s).