Release 10.1A: OpenEdge Data Management:
SQL Reference

> ## CREATE VIEW

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

> Syntax

>     CREATE VIEW [ owner_name.]view_name
>       [ ( column_name, column_name,... ) ]
>       AS [ ( ] query_expression [ ) ]
>       [ WITH CHECK OPTION ] ;

> ``owner_name``

> Owner of the created view.

> `( column_name, column_name,... )`

> Specifies column names for the view. These names provide an alias for the columns selected by the query specification. If the column names are not specified, then the view is created with the same column names as the tables or views on which it is based.

> `WITH CHECK OPTION`

> Checks that the updated or inserted row satisfies the view definition. The row must be selectable using the view. The `WITH` `CHECK` `OPTION` clause is only allowed on an updatable view.

> #### Notes

> - 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` clause, 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 (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 (it satisfies all the conditions specified above for update ability).
> - 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.
> Example

>     CREATE VIEW ne_customers AS
>               SELECT name, address, city, state
>               FROM customer
>               WHERE state IN ( 'NH', 'MA', 'ME', 'RI', 'CT', 'VT' )
>               WITH CHECK OPTION ;
>     CREATE VIEW OrderCount (custnum, numorders) AS
>               SELECT CustNum, COUNT(*)
>               FROM Order
>               GROUP BY CustNum;

> ##### Authorization

> Must have `DBA` privilege, `RESOURCE` privilege, or `SELECT` privilege.

> ##### Related statements

> DROP VIEW

Copyright © 2005 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095