|
|
The CREATE VIEW statement creates a view of a table, another view, or
a combination of tables and views.
ISQL or Application Programs
CREATE VIEW [Owner.]ViewName [(ColumnName[,...])]
AS QueryExpression [IN DBEFileSetName]
[WITH CHECK OPTION [CONSTRAINT ConstraintID]]
- [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.)
- ConstraintID
is the optional name of the view check constraint.
A view definition with * in the select list generates a view that
refers to all the columns that exist in the base table(s) at the time
the view is created. Adding new columns to the base tables does not
cause these columns to be added to the view.
A view is said to be updatable when you can use it in DELETE,
UPDATE, or INSERT statements to modify the base table. A
view is updatable only if the query from which it is derived matches
the following updatability criteria:
No DISTINCT, GROUP BY, or HAVING clause is specified in the
outermost SELECT clause, and no aggregate appears in its select
list.
The FROM clause specifies exactly one table, which must be an
updatable table. See "Updatability of Queries" in the
"SQL Queries" chapter.
To use INSERT and UPDATE statements through
views, the select list in the view definition must not contain
any arithmetic expressions. It must contain only column
names.
For DELETE WHERE CURRENT and UPDATE WHERE
CURRENT statements operating on cursors defined with views,
the view definition must not contain subqueries.
For noncursor UPDATE, DELETE, and INSERT
statements, the view definition must not contain any subqueries
which contain in their FROM clauses a table reference to the same
table as the outermost FROM clause.
You cannot define an index on a view or alter a view.
You cannot use host variables, local variables, procedure
parameters, or dynamic parameters in the CREATE VIEW
statement.
Creating a view causes a section to be stored in the system
catalog. A description of the section appears in the SYSTEM.SECTION
view.
If you use the CREATE VIEW statement within the CREATE
SCHEMA statement, the default owner of the view is the schema's
AuthorizationName.
When you create a view, an entry containing the SELECT
statement in the view definition is stored in the SYSTEM.VIEWDEF view
in the system catalog. The view's name is stored in SYSTEM.TABLE, and
the description of its columns appears in SYSTEM.COLUMN.
If you use the CREATE VIEW statement with a CREATE
PROCEDURE statement, the default owner is the procedure
owner.
Any attempt to write through a view defined having a WITH CHECK
OPTION must satisfy any conditions specified in the query
specification. All underlying view definitions are also checked. Any
constraints in the table on which the view is based are also
checked.
View check constraints are not deferrable.
To drop a constraint on a view, you must drop the view and
recreate it without the constraint.
You cannot use an ORDER BY clause when defining a view.
If the IN DBEFileSetName clause is specified, but
the view owner does not have SECTIONSPACE authority for the specified
DBEFileSet, a warning is issued and the default SECTIONSPACE
DBEFileSet is used instead. (Refer to syntax for the GRANT statement
and the SET DBEFILESET statement.)
You can create a view if you have SELECT or OWNER authority for the tables and
views mentioned in the FROM clause of the SELECT statement or if you
have DBA authority. To operate on a table on which the view is based, the
authority you need depends on whether or not you own the view. The authority
needed in either case is specified as follows:
If you own the view, you need authority for the table(s) or
view(s) on which the view is based.
If you do not own the view, you need authority granted
specifically for the view.
To specify a DBEFileSetName for a view, the view owner must
have SECTIONSPACE authority on the referenced DBEFileSet.
The following view provides information on the value of current
orders for each vendor. Because the view is derived by joining
tables, the base tables cannot be updated via this view.
CREATE VIEW PurchDB.VendorStatistics
(VendorNumber,
VendorName,
OrderDate,
OrderQuantity,
TotalPrice)
AS SELECT PurchDB.Vendors.VendorNumber,
PurchDB.Vendors.VendorName,
OrderDate,
OrderQty,
OrderQty*PurchasePrice
FROM PurchDB.Vendors,
PurchDB.Orders,
PurchDB.OrderItems
WHERE PurchDB.Vendors.VendorNumber =
PurchDB.Orders.VendorNumber
AND PurchDB.Orders.OrderNumber =
PurchDB.OrderItems.OrderNumber
IN PurchDBFileSet
The following view is updatable because it is created from one
table. When the table is updated through the view, column values in
the SET or VALUES clause are checked against the WHERE clause in the
view definitions. If the table on which the view is based has any
check constraints of its own, these conditions are checked along with
the WITH CHECK OPTION of the view.
CREATE VIEW RecDB.EventView
(Event,
Date)
AS SELECT RecDB.Event,
RecDB.Date
FROM RecDB.Events
WHERE Date >= CURRENT_DATE
WITH CHECK OPTION CONSTRAINT EventView_WCO
|