Description
The syntax of the CREATE VIEW statement is similar to that of CREATE TABLE...AS SELECT. However, data is not retrieved when a view is created. Instead, the view definition is stored and, when the view_name is later used in an SQL statement, the statement operates on the tables that are used to define the view. (The tables or views used to define a view are called its base tables.)
All selects on views are fully supported. Simply use a view_name in place of a tablename in any SQL retrieval. However, updates, inserts, and deletes on views are subject to several rules:
• The view was created from more than one table.
• The view was created from a non‑updatable view.
• Any columns in the view are derived from an expression or aggregate (set) function.
• Additionally, inserts are not allowed if:
– The view definition contains a where clause and specifies the with check option (if supported by the Enterprise Access product).
– If any column in the underlying table that was declared as not null not default is not present in the view.
• The ability to update a view depends in part on whether the with check option is specified.
When a view is created WITH CHECK OPTION, columns that are part of the view’s qualification cannot be updated.
If the WITH CHECK OPTION is not specified, any row in the view can be updated, even if the update results in a row that is no longer a part of the view.
For example, consider the following two statements:
create view v
as select *
from t
where c = 10;
update v
set c = 5;
Because the WITH CHECK OPTION is not specified in the view’s definition, you are allowed to update column c. If the view had been created WITH CHECK OPTION, the update would not be allowed.
By default, WITH CHECK OPTION is not set.
When a table used in the definition of a view is dropped, the view is also dropped.
Note: Particular Enterprise Access products may support extensions to the CREATE VIEW statement, using the WITH clause.
To ensure application portability, follow every CREATE VIEW statement with a COMMIT statement.