create view syntax

Here is the full syntax for create view:

create view [owner .]view_name  
     [(column_name  [, column_name ]...)] 
     as select [distinct] select_statement
     [with check option]

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.

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.

Local variables cannot be used in view definitions.