2.12 CREATE VIEW

---

Description

Creates a view with the specified name on existing tables and/or views.

Syntax

CREATE VIEW [ owner_name. ] view_name

[ ( column_name, column_name,... ) ]

AS [ ( ] query_expression [ ) ]

[ WITH CHECK OPTION ] ;

Notes

The owner_name is made the owner of the created view.
The column names specified for the view are optional and provide an alias for the columns selected by the query specification. If the column names are not specified then the view will be created with the same column names as the tables and/or views it is based on.
A view is deletable if deleting rows from that view is allowed. For a view to be deletable, the view definition has to satisfy the following conditions:
The first FROM clause contains only one table reference or one view reference.
There are no aggregate functions, DISTINCT clause, GROUP BY or HAVING clause in the view definition.
If the first FROM clause contains a view reference, then the view referred to is deletable.
A view is updatable if updating rows from that view is allowed. For a view to be updatable, the view has to satisfy the following conditions:
The view is deletable (That is, it satisfies all the conditions specified above for deletability).
All the select expressions in the first SELECT clause of the view definition are simple column references.
If the first FROM clause contains a view reference, then the view referred to is updatable.
A view is insertable if inserting rows into that view is allowed. For a view to be insertable, the view has to satisfy the following conditions:
The view is updatable (That is, it satisfies all the conditions specified above for updatability).
If the first FROM clause contains a table reference, then all NOT NULL columns of the table are selected in the first SELECT clause of the view definition.
If the first FROM clause contains a view reference, then the view referred to is insertable.
The WITH CHECK OPTION clause can be specified only if the view is updatable.
If WITH CHECK OPTION clause is specified when defining a view, then during any update or insert of a row on this view, it is checked that the updated/inserted row satisfies the view definition (That is, the row is selectable using the view).

Examples

CREATE VIEW ne_customers AS

SELECT cust_no, name, street, city, state, zip

FROM customer

WHERE state IN ('NH', 'MA', 'NY', 'VT')

WITH CHECK OPTION ;

CREATE VIEW order_count (cust_number, norders) AS

SELECT cust_no, COUNT(*)

FROM orders

GROUP BY cust_no;

Authorization

The user executing this statement must have the following privileges:

DBA or RESOURCE privilege.
SELECT privilege on all the tables/views referred to in the view definition.

If owner_name is specified and is different from the name of the user executing the statement, then the user must have DBA privilege.

SQL Compliance	SQL-92, ODBC Core SQL grammar
Environment	Embedded SQL, interactive SQL, ODBC applications
Related Statements	Query Expressions, DROP VIEW