CREATE VIEW

Purpose

Use this statement to create a view.

Prerequisites

  • You need to have the system privilege CREATE VIEW, if the view is created in your own schema or in that of an assigned user role, or CREATE ANY VIEW. Additionally, the owner of the view (who is not automatically the creator) must posses the corresponding SELECT privilege on all the objects referenced in the subquery.
  • If the OR REPLACE option is specified and the view already exists, then the user also needs the same rights as for DROP VIEW.

Syntax

create_view::=

Create View

Create View

Create View

Usage Notes

  • An already existing view can be replaced with the OR REPLACE option, without having to explicitly delete the view with the DROP VIEW statement. Keep in mind that existing object privileges on the view are dropped when replacing a view.
  • By specifying the FORCE option, you can create a view without compiling its text. This can be very useful if you want to create many views which depend on each other and which otherwise must be created in a certain order. Note that syntax or other errors will not be found until the view is first used.
  • By specifying the column aliases, you can define the column names of the view. If the column aliases are not specified, then the column names are derived from the subquery. If the columns are not directly referenced in the subquery but assembled in expressions, then you must specify aliases for these either within the subquery or from the entire view.
  • The view creation text is limited to 20.000 characters.
  • A view is set to INVALID if one of its underlying objects was changed (see also column STATUS in system tables, for example, EXA_ALL_COLUMNS). At the next read access, the system tries to automatically recompile the view. If this is successful, the view is valid again. Otherwise, an appropriate error message is thrown. This means in particular that an invalid view can still be usable.
  • The view comment is visible when performing a DESC[RIBE] FULL command for the view.

Example

CREATE VIEW my_view as select x from t;
CREATE OR REPLACE VIEW my_view as select y from t;
CREATE OR REPLACE VIEW my_view (col_1) as select max(y) from t;