CREATE VIEW

---

Creates a view on one or more tables or views.

Usage

Embedded/Interactive/ODBC/JDBC.

Description

View-name

If view-name is specified in its unqualified form, the view will be created in the schema which has the same name as the current ident.

If view-name is specified in its fully qualified form (i.e. schema-name.view-name) the view will be created in the named schema (in this case, the current ident must be the creator of the specified schema).

Column-name

If a list of column names is given in parentheses before the query-expression, the columns in the view are named in accordance with this list. There must be the same number of names in the column list as there are columns addressed by the query-expression. The names must be unique within the list.

If the column name list is omitted, the columns in the view will be given the same names as they have in the source table(s) or view(s) addressed in the select-specification. The column names in the source must all be unique in the view being created. If this is not the case, an explicit column name list must be given. An explicit column name list must also be given if columns in the view are defined as expressions without correlation names.

select-expression

A view is created in accordance with the specification in the query-expression, see SELECT for more information on select-expression's.

WITH CHECK OPTION

Specification of WITH CHECK OPTION indicates that any data inserted into the view by INSERT or UPDATE statements will be checked for conformity with the definition of the view. Attempts to insert data which do not conform to the view definition will be rejected.

The optional keyword CASCADED can be explicitly specified in the WITH CHECK OPTION clause to ensure that any data inserted into a view which is based on this view will be also be checked for conformity with the definition of this view.

Thus, if an INSERT or UPDATE in a view based on this one results in an attempt to insert data into this view which does conform to the view definition, the data change operation will be rejected.

If CASCADED is not specified, it is assumed by default (use of the keyword CASCADED is now permitted to allow for future extensions to the Mimer SQL syntax).

Language Elements

query-expression, see SELECT.

Restrictions

CREATE VIEW requires SELECT access to the tables or views from which the view is created, and EXECUTE privilege on routines and USAGE privilege on sequences, domains and user-defined types referenced.

Notes

The view name may not be the same as the name of any other table, view or synonym belonging to the same schema.

The creator of the view is always granted SELECT access to the view. If the view is updatable, see below, any access the creator may hold on the underlying table or view at the time the new view is created is also granted on the new view. Access to the view is granted WITH GRANT OPTION only if the corresponding access to all underlying tables, views, routines, sequences, domains and user-defined types are held WITH GRANT OPTION.

SELECT and UPDATE statements can only be performed on data accessible from the view. Insertion of a new row assigns the default value or NULL value to columns in the base table excluded from the view, in accordance with the definition of the columns. Deletion of a row from a view removes the entire row from the underlying base table, including columns invisible from the view.

The select-specification defining the view may not contain references to host variables.

The WITH CHECK OPTION clause is illegal if the view is not updatable. A result set is only updatable if all of the following conditions are true:

• the keyword DISTINCT is not specified
• the FROM clause specifies exactly one table reference and that table reference refers either to a base table or an updatable view
• a GROUP BY clause is not included
• a HAVING clause is not included.
• the result set is not the product of an explicit INNER or OUTER JOIN
• the keyword UNION is not included
• the keyword EXCEPT is not included
• the keyword INTERSECTION is not included

A view will always be updatable if an INSTEAD OF trigger exists on the view, regardless of the conditions previously mentioned. If there is an INSTEAD OF trigger any possible with check option for the view is ignored. If all the INSTEAD OF triggers on the view are dropped, the view will revert to not updatable if one or more of those conditions are not true.

If an updatable view is based on other views, insert and update operations are checked against all view definitions for which WITH CHECK OPTION is specified. Thus if view-2 is defined with check option on view-1, which in turn is defined with check option on a base table, no changes may be made in the base table through either view-1 or view-2 which violate the definition of view-1.

Example

 CREATE VIEW mimer_store_book.details
    AS SELECT authors_list, product AS title, producer AS publisher, format,
              price, stock, reorder_level,
              extract_date(release_date) AS release_date,
              'ISBN:99-999-9999-9' as ISBN,  -- *****
              -- *****'ISBN:' || mimer_store_book.format_isbn(isbn) AS isbn,
              ean_code, status, product_search AS title_search,
              product_details.item_id, category_id, product_id,
              display_order, image_id
    FROM product_details
      JOIN mimer_store_book.titles
        ON product_details.item_id = mimer_store_book.titles.item_id;

Standard Compliance

Standard	Compliance	Comments
SQL-2011	Core	Fully compliant.

Mimer Information Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 info@mimer.se