CREATE VIEW

The CREATE VIEW data description statement defines a view in the dictionary.

Authorization

To issue a CREATE VIEW statement, you must own the schema where the view is being defined or hold the CREATE privilege on the named view.

Syntax

►►─── CREATE VIEW ─┬────────────────┬─ view-identifier ───────────────────────►
                   └─ schema-name. ─┘

 ►─┬────────────────────────────────┬─────────────────────────────────────────►
   │     ┌──────── , ─────────┐     │
   └─ ( ─▼─ view-column-name ─┴─ ) ─┘

 ►─── AS query-expression ────────────────────────────────────────────────────►

 ►─┬──────────────────────────┬───────────────────────────────────────────────►
   └─ order-by-specification ─┘

 ►─┬─────────────────────┬────────────────────────────────────────────────────►
   └─ WITH CHECK OPTION ─┘

 ►────┬───────────────────────────────────────────┬───────────────────────────►◄
      └── TIMESTAMP  timestamp-value ─────────────┘

Expansion of order-by-specification

               ┌─────────────────────── , ───────────────────────────┐
►►── ORDER BY ─▼─┬─┬───────────────┬─ column-name ─┬───┬──────────┬──┴────────►◄
                 │ ├─ table-name. ─┤               │   ├─ ASC ◄──┤
                 │ └─ alias. ──────┘               │   └─ DESC ───┘
                 ├─ column-number ─────────────────┤
                 ├─ result-name ───────────────────┤
                 └─ rowid-pseudo-column ───────────┘

Parameters

view-identifier

Specifies the name of the view being created. View-identifier must be a 1- through 18-character name that follows the conventions for SQL identifiers.

View-identifier must be unique among the table, view, procedure and table procedure identifiers within the schema associated with the view.

schema-name

Specifies the schema to be associated with the view. Schema-name must identify a schema defined in the dictionary.

If you do not specify schema-name, it defaults to:

The current schema associated with your SQL session, if the statement is entered through the Command Facility or executed dynamically
The schema associated with the access module used at runtime, if the statement is embedded in an application program

( view-column-name )

Assigns names to the columns to be included in the view. The number of column names must be the same as the number of columns in the result table represented by query-expression. The first column name is assigned to the first column in the result table, the second column name to the second result column, and so on.

Column-name must be a 1- through 32-character name that follows the conventions for SQL identifiers and must be unique within the view being defined.

The list of column names must be enclosed in parentheses. Multiple column names must be separated by commas.

If you do not specify any column names, CA IDMS assigns to the columns in the view the same names as those of the result table of query-expression.

AS query-expression

Defines the columns to be included in the view. The first column in the result table is the first column in the view, the second result column is the second column in the view, and so on.

Note: For more information about expanded query-expression syntax, see Expansion of Query-expression.

order-by-specification

Specifies a sort order for the rows in the result table defined by query-expression. Expanded syntax for order-by-specification is shown immediately following the CREATE VIEW syntax.

The use of the ORDER BY parameter in a CREATE VIEW statement is a CA IDMS extension of the SQL standard.

WITH CHECK OPTION

Specifies that any row inserted or updated through the view must satisfy the search condition of the WHERE clause in the query specification. This means you cannot add data through a view that the view would prevent you from retrieving.

TIMESTAMP timestamp-value

specifies the value of the synchronization stamp to be assigned to the view. Timestamp-value must be a valid external representation of a timestamp.

Parameters for Expansion of order-by-specification

ORDER BY

Sorts the rows in the result table defined by query-expression in ascending or descending order by the values in the specified columns. Rows are ordered first by the first column specified, then by the second column specified within the ordering established by the first column, then by the third column specified, and so on.

You can specify from 1 through 254 columns in the ORDER BY parameter. Multiple columns must be separated by commas.

column-name

Specifies a sort column by name. Column-name must identify a column in the result table of the query expression.

table-name: Specifies the table, view, procedure or table procedure that includes the named column. For expanded table-name syntax, see Identifying Entities in Schemas.
alias: Specifies the alias associated with the table, view, procedure or table procedure that includes the named column. Alias must be defined in the FROM parameter of the query specification that makes up the query expression.

column-number

Specifies a sort column by the position of the column in the result table defined by query-expression. The first result column is in position 1.

Column-number must be an integer in the range 1 through the number of columns in the result table.

result-name

Specifies the sort column by the result name specified in the AS parameter of query-expression.

rowid-pseudo-column

Specifies a sort column as a ROWID pseudo-column. See Expansion of rowid-pseudo-column.

ASC

Indicates that the values in the specified column are to be sorted in ascending order. ASC is the default when you specify neither ASC nor DESC.

DESC

Indicates that the values in the specified column are to be sorted in descending order.

Usage

Views on SYSTEM Tables

You can define a view on a table in the SYSTEM schema, but you cannot associate the view with the SYSTEM schema.

Required Column Names

You must include column names in a CREATE VIEW statement when any one of the following is true:

Two or more of the result columns specified in the query-expression have the same name
One or more of the value expressions representing the columns in the result table include a literal, an arithmetic operation (unary or binary), or an aggregate function
A column in the result table has been assigned an alias through the AS parameter of the query specification

Restriction on query-expression

In a CREATE VIEW statement, query-expression cannot include:

Host variables, local variables, or routine parameters
References to temporary tables

Grouped Views

If the query-expression in a CREATE VIEW statement includes a GROUP BY or HAVING parameter that is not contained in a subquery, the view defined by the statement is a grouped view.

Updateable Views

For a view to be updateable:

The query-expression must be updateable
The view definition must not contain an ORDER BY clause

Result columns derived from a value expression other than a simple column reference cannot be updated or inserted through a view.

Using WITH CHECK OPTION

WITH CHECK OPTION has meaning only if the view is updateable and cannot be specified if the WHERE clause of the query expression contains a subquery.

When a view defined with WITH CHECK OPTION is referenced in the FROM clause of a second view definition, the check criterion of the original view is applied to data inserted or updated through the second view. If the second view is part of a third view definition, the check criterion of the original view is applied to data inserted or updated through the third view, and so on.

If a view defined without WITH CHECK OPTION is referenced in the FROM clause of a second view that has a WITH CHECK OPTION, the search conditions in the WHERE clause of both view definitions must be satisfied by an UPDATE or INSERT statement that references the second view. This principle holds true regardless of the number of levels of view references involved.

Once WITH CHECK OPTION is encountered in a view definition, all subordinate views referenced by that view are treated as if their definitions also contain WITH CHECK OPTION.

Use of * in a View Definition

Avoid the use of * in the query expression to denote all columns of a table named in the FROM parameter. If * is used and new columns are added to the table, the view becomes invalid; it must be dropped and recreated.

Altering the definition of an underlying table does not impact the view if you explicitly identify columns in the view definition.

Specifying a Synchronization Stamp

When defining a view, you can specify a value for its synchronization stamp. You should use care when doing so because the purpose of the stamp is to enable the detection of discrepancies between an entity and its definition. If explicitly specified, you must set the synchronization stamp to a new value following a change so that the change is detectable by the runtime system.

If not specified, the synchronization stamp is automatically set to the current date and time.

Note: For more information about dropping view definitions, see DROP VIEW.

Examples

Specifying Column Names in a View Definition

The following CREATE VIEW statement defines a view with three columns derived from two tables. The definition of the third column includes aggregate functions and a binary arithmetic operation. Therefore, the CREATE VIEW statement must specify names for all the columns in the view.

create view emp_vacation
   (emp_id, dept_id, vac_time)
   as select e.emp_id, dept_id, sum(vac_accrued) - sum(vac_taken)
      from employee e, benefits b
      where e.emp_id = b.emp_id
      group by dept_id, e.emp_id;

Defining an Updateable View

The following CREATE VIEW statement defines an updateable view:

create view emp_home_info
   as select emp_id, emp_lname, emp_fname, street, city, state,
         zip_code, phone
      from employee;