SQL Reference Guide > SQL Reference Guide > SQL Statements > CREATE VIEW
Was this helpful?
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 [WITH common_table_expression] select_stmt
              [WITH CHECK OPTION]
view_name
Defines the name of the view. It must be a valid object name.
AS [WITH common_table_expression] select_stmt
Creates a view from the criteria specified in the select_stmt. The select_stmt is a SELECT statement, as described in the SELECT statement description in this chapter.
The SELECT can be preceded by a common table expression (see WITH (common_table_expression)).
WITH CHECK OPTION
Prevents an INSERT or UPDATE to a view that creates a row that does not comply with the view definition. For details, see WITH CHECK OPTION Clause.
The CREATE VIEW statement uses a SELECT statement to define the contents of a virtual table. The view definition is stored in the system catalogs. When the view is used in a statement, the statement operates on the underlying tables. When a table or view used in the definition of a view is dropped, the view is also dropped.
Data can be selected from a view the same way data is selected from a base table. However, updates, inserts, and deletes on views are subject to several restrictions. Updates, inserts, and deletes are allowed only if the view meets all the following conditions:
• The view is based on a single updatable table or view.
• All columns see columns in the base table or view (no aggregate functions or derived columns are allowed).
• The SELECT statement omits DISTINCT, GROUP BY, HAVING, ORDER BY, and UNION clauses.
Inserts are not allowed if a mandatory (not null not default) column in a base table is omitted from the view.
A maximum of 1024 columns can be specified for a view.
Note:  This statement has additional considerations when used in a distributed environment. For more information, see the Star User Guide.
Embedded Usage
In an embedded program, constant expressions can be expressed in the select_stmt with host language string variables. If the select_stmt includes a WHERE clause, a host language string variable can be used to specify the entire WHERE clause qualification. Specify the WITH clause using a host string variable (with :hostvar).
Permissions
You must have all privileges required to execute the SELECT statements that define the view.
Note:  Such privileges must be granted at the user level (not group or role level) or else the attempt to define the view will generate an error.
Locking
The CREATE VIEW statement requires an exclusive lock on the view's base tables.
Related Statements
DROP
INSERT
SELECT
Last modified date: 08/14/2024