ALLBASE/SQL Reference Manual (36216-90216)

Chapter 10 367
SQL Statements A - D
CREATE VIEW
CREATE VIEW
The CREATE VIEW statement creates a view of a table, another view, or a combination of
tables and views.
Scope
ISQL or Application Programs
SQL Syntax
CREATE VIEW [ Owner. ] ViewName [( ColumnName [,...])]
AS QueryExpression [IN DBEFileSetName ]
[WITH CHECK OPTION [CONSTRAINT ConstraintID ]]
Parameters
[ Owner .] ViewName is the name to be assigned to the view. One owner cannot own more
than one view with the same name. The view name cannot be the same as
the table name.
You can specify the owner of the new view if you have DBA authority.
Non-DBA users can specify as owner the name of any group of which they
are a member. If you do not specify the owner name, your DBEUserID,
schema authorization name, procedure owner, or the ISQL SET OWNER
name becomes the owner of the new table. For more information, refer to
the section "Default Owner Rules" in the chapter "Using ALLBASE/SQL."
ColumnName specifies the names to be assigned to the columns of the new view. The
names are specified in an order corresponding to the columns of the query
result produced by the query expression. You can specify a maximum of
1023 columns for a view.
You must specify the column names if any column of the query result is
defined by a computed expression, aggregate function, reserved word, or
constant in the select list of the query expression. You must also specify
column names if the same column name (possibly from different table)
appears in the select list more than once.
If you do not specify column names, the columns of the view are assigned
the same names as the columns from which they are derived. The * is
expanded into the appropriate list of column names.
QueryExpression is the query expression from which the view is derived. The select list
can contain as many as 1023 columns. The query expression may refer to
tables or views or a combination of tables and views. The query expression
may include UNION and/or UNION ALL operations.
DBEFileSetName specifies the DBEFileSet to be used for storing the section associated
with the view. If not specified, the default SECTIONSPACE DBEFileSet is
used. (Refer to syntax for the SET DEFAULT DBEFILESET statement.)