As illustrated in the create view example in “View examples”, you need not specify any column names in the create clause of a view definition statement. Adaptive Server gives the columns of the view the same names and datatypes as the columns referred to in the select list of the select statement. The select list can be designated by the asterisk (*), as in the example, or it can be a full or partial list of the column names in the base tables. See the Reference Manua:Commands.
To build views that do not contain duplicate rows, use the distinct keyword of the select statement to ensure that each row in the view is unique. However, you cannot update distinct views.
You can always specify column names. However, if any of the following are true, you must specify column names in the create clause for every column in the view:
One or more of the view’s columns are derived from an arithmetic expression, an aggregate, a built-in function, or a constant.
Two or more of the view’s columns would otherwise have the same name. This usually happens because the view definition includes a join, and the columns being joined have the same name.
You want to give a column in the view a different name than the column from which it is derived.
You can also rename columns in the select statement. Whether or not you rename a view column, it inherits the datatype of the column from which it is derived.
Here is a view definition statement that makes the name of a column in the view different from its name in the underlying table:
create view pub_view1 (Publisher, City, State) as select pub_name, city, state from publishers
Here is an alternate method of creating the same view but renaming the columns in the select statement:
create view pub_view2 as select Publisher = pub_name, City = city, State = state from publishers
The examples of view definition statements in the next section illustrate the rest of the rules for including column names in the create clause.
You cannot use local variables in view definitions.
