The CREATE VIEW data description statement defines a view in the dictionary.
The CREATE VIEW data description statement defines a view in the dictionary.
This article describes the following information:
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.
►►─── CREATE VIEW ─┬────────────────┬─view-identifier───────────────────────► └─schema-name. ─┘ ►─┬────────────────────────────────┬─────────────────────────────────────────► │ ┌──────── , ─────────┐ │ └─ ( ─▼─view-column-name─┴─ ) ─┘ ►─── ASquery-expression────────────────────────────────────────────────────► ►─┬──────────────────────────┬───────────────────────────────────────────────► └─order-by-specification─┘ ►─┬─────────────────────┬────────────────────────────────────────────────────► └─ WITH CHECK OPTION ─┘ ►────┬───────────────────────────────────────────┬───────────────────────────►◄ └── TIMESTAMPtimestamp-value─────────────┘
Expansion of order-by-specification
┌─────────────────────── , ───────────────────────────┐ ►►── ORDER BY ─▼─┬─┬───────────────┬─column-name─┬───┬──────────┬──┴────────►◄ │ ├─table-name.─┤ │ ├─ ASC ◄───┤ │ └─alias.──────┘ │ └─ DESC ───┘ ├─column-number─────────────────┤ ├─result-name───────────────────┤ └─rowid-pseudo-column───────────┘
- view-identifierSpecifies the name of the view being created.View-identifiermust be a 1- through 18-character name that follows the conventions for SQL identifiers.View-identifiermust be unique among the table, view, procedure and table procedure identifiers within the schema associated with the view.
- schema-nameSpecifies the schema to be associated with the view.Schema-namemust identify a schema defined in the dictionary.If you do not specifyschema-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 byquery-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-namemust 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 ofquery-expression.
- AS query-expressionDefines 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.
- order-by-specificationSpecifies a sort order for the rows in the result table defined byquery-expression. Expanded syntax fororder-by-specificationis 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 OPTIONSpecifies 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.
- TIMESTAMPtimestamp-valuespecifies the value of the synchronization stamp to be assigned to the view.Timestamp-valuemust be a valid external representation of a timestamp.
Parameters for Expansion of order-by-specification
- ORDER BYSorts the rows in the result table defined byquery-expressionin 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-nameSpecifies a sort column by name.Column-namemust identify a column in the result table of the query expression.
- table-nameSpecifies the table, view, procedure or table procedure that includes the named column. For expandedtable-namesyntax, see Identifying Entities in Schemas.
- aliasSpecifies the alias associated with the table, view, procedure or table procedure that includes the named column.Aliasmust be defined in the FROM parameter of the query specification that makes up the query expression.
- column-numberSpecifies a sort column by the position of the column in the result table defined byquery-expression. The first result column is in position 1.Column-numbermust be an integer in the range 1 through the number of columns in the result table.
- result-nameSpecifies the sort column by the result name specified in the AS parameter ofquery-expression.
- rowid-pseudo-columnSpecifies a sort column as a ROWID pseudo-column. See Expansion of rowid-pseudo-column.
- ASCIndicates 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.
- DESCIndicates that the values in the specified column are to be sorted in descending order.
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,
- Host variables, local variables, or routine parameters
- References to temporary tables
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.
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
withWITH 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
withoutWITH 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.
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;