 |
» |
|
|
|
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.) - ConstraintID
is the optional name of the view check constraint.
Description |  |
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.)
Authorization |  |
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. Examples |  |
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
|