HP 3000 Manuals HP 3000 Manuals
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 bethe 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
1. 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
2. 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