CREATE VIEW

The CREATE VIEW statement creates a view of the tables specified in the SelectQuery clause. A view is a logical table that is based on one or more detail tables. The view itself contains no data. It is sometimes called a nonmaterialized view to distinguish it from a materialized view, which does contain data that has already been calculated from detail tables.

Required privilege

The user executing the statement must have the CREATE VIEW privilege (if owner) or CREATE ANY VIEW (if not the owner) for another user's view.

The owner of the view must have the SELECT privilege on the detail tables.

SQL syntax

CREATE VIEW ViewName AS SelectQuery

Parameters

The CREATE VIEW statement has the parameters:

Parameter	Description
`ViewName`	Name assigned to the new view.
`SelectQuery`	Selects column from the detail tables to be used in the view. Can also create indexes on the view.

Restrictions on the select query

There are several restrictions on the query that is used to define the view.

A SELECT * query in a view definition is expanded when the view is created. Any columns added after a view is created do not affect the view.
Do not create a view with the same name as a sequence.
The following cannot be used in a SELECT statement that is used to create a view:
- DISTINCT
- FIRST
- ORDER BY
- Arguments
- Temporary tables
Each expression in the select list must have a unique name. A name of a simple column expression would be that column's name unless a column alias is defined. ROWID is considered an expression and needs an alias.
No SELECT FOR UPDATE or SELECT FOR INSERT statements can be used to create a view.
Certain TimesTen query restrictions are not checked when a nonmaterialized view is created. Views that violate those restrictions may be allowed to be created, but an error is returned when the view is referenced later in an executed statement.

Restrictions on the select query

When a view is referenced in the FROM clause of a SELECT statement, its name is replaced by its definition as a derived table at parsing time. If it is not possible to merge all clauses of a view to the same clause in the original select query to form a legal query without the derived table, the content of this derived table is materialized. For example, if both the view and the referencing select specify aggregates, the view is materialized before its result can be joined with other tables of the select.
A view cannot be dropped with a DROP TABLE statement. You must use the DROP [MATERIALIZED] VIEW statement.
A view cannot be altered with an ALTER TABLE statement.
Referencing a view can fail due to dropped or altered detail tables.

Examples

Create a nonmaterialized view from the employees table.

Command> CREATE VIEW v1 AS SELECT employee_id, email FROM employees;
Command> SELECT FIRST 5 * FROM v1;
< 100, SKING >
< 101, NKOCHHAR >
< 102, LDEHAAN >
< 103, AHUNOLD >
< 104, BERNST >
5 rows found.

Create a nonmaterialized view from an aggregate query on the table t1.

CREATE VIEW v1 (max1) AS SELECT MAX(x1) FROM t1;