Switch to: V9V8V7V6V5

CREATE VIEW

CREATE VIEW statement is used to create a virtual table in the database.

Syntax

view_definition
    : CREATE [OR REPLACE] VIEW view_name [ (column_name_list) ]
      AS query_expression
      [ WITH CHECK OPTION ]

Arguments

OR REPLACE

This clause forces dropping of an existed table with the specified name and then new empty table will be create as specified. So effectively this is the same as

DROP TABLE T;
CREATE TABLE T ....

ATTENTION: 'CREATE OR REPLACE' will destroy all sub-objects of the existed view, such as triggers, constraints. If you need preserve sub-objects, use ALTER VIEW command.

If table do not exists, then nothing happens.

NOTE: 'OR REPLACE' syntax Valentina have take from Oracle/Postgre world.

IF NOT EXISTS

The IF NOT EXISTS option allows you supress the error message in case if such view already exists. This makes it much easier to perform SQL dumps without interruption.

NOTE: 'IF NOT EXISTS' syntax Valentina have take from mySQL world.

view_name

Specifies the name of new view. A view belongs to a database. Tables and Views share the same namespace within a database, so a database cannot contain a table and a view that have the same name.

column_name_list

Views must have unique column names with no duplicates, just like base tables. By default, the names of the columns retrieved by the SELECT statement are used for the view column names. To define explicit names for the view columns, the optional column_list clause can be given as a list of comma-separated identifiers. The number of names in column_list must be the same as the number of columns retrieved by the SELECT statement.

Columns retrieved by the SELECT statement can be simple references to table columns. They can also be expressions that use functions, constant values, operators, and so forth.

AS query_expression

Specifies a SELECT statement that provides the definition of the view.

WITH CHECK OPTION

The WITH CHECK OPTION clause can be given for an updatable view to prevent inserts or updates to rows except those for which the WHERE clause in the select_statement is true.

Notes

  • Valentina 3.0 have read-only views.

Examples

CREATE VIEW wMale AS 
SELECT * FROM tblPerson WHERE fldGender = 'M';