CREATE VIEW

The CREATE VIEW data description statement defines a view in the dictionary.
idmscu19
The CREATE VIEW data description statement defines a view in the dictionary.
This article describes the following information:
2
2
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.
    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 for the following reasons.
  • 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.
  • The use of * for columns of a table in a schema with virtual keys returns the ROWID and the virtual foreign columns in the column list. If multiple tables are referenced in the query-expression, the ROWID columns are ambiguous and an error is returned due to duplicate column names.
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.
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;