CREATE VIEW
Valid in: SQL, ESQL, OpenAPI, ODBC, JDBC, .NET
The CREATE VIEW statement defines a virtual table.
The CREATE VIEW statement has the following format:
[EXEC SQL] CREATE VIEW view_name
[(column_name {, column_name})]
AS subselect {UNION [ALL] subselect}
[WITH with_clause]
view_name
Defines the name of the view. It must be a valid object name.
subselect
Specifies a SELECT clause, as described in the Select statement description in this chapter.
WITH with_clause
Specifies Enterprise Access product-specific options. For details,see your Enterprise Access product guide.For an overview of the Enterprise Access product WITH clause, see the chapter "OpenSQL Features."
Enterprise Access products that do not support the WITH CHECK OPTION will ignore it when creating the specified view.
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.
Last modified date: 01/30/2023