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

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}}