Skip to end of metadata
Go to start of metadata

Name

CREATE VIEW—define a new view

Syntax

Description

CREATE VIEW defines a view of a query.  The view is not physically materialized.  Instead, the query is run every time the view is referenced in a query.  The column_name list is optional.  However, if the column_name list is provided, the number of columns in the column_name list must be identical to the number of columns in the SELECT query.
If a schema name is specified (for example, CREATE VIEW myschema.myview ...) then the view is created in the specified schema; otherwise it is created in the current schema.  The view name can be the same as names for sequences and indexes.  However, the view name must be distinct from the name of any other view or table in the schema that the view is being created in.
Use DROP VIEW to drop views. To change a view, drop and recreate it.

Parameters

schema

Name of the schema in which the view will be created. The default is the current schema as defined by the USE command. If schema is provided, and the schema does not exist, it will be created.

view

Name (optionally schema-qualified) of a view to be created.

column_name

An optional list of column names to be used as columns for the view. If specified, these names override the column names that would be based on the SELECT query

query

A SELECT statement (see SELECT) which provides the columns and rows of the view. If joining multiple tables, and selecting a column that is common in joined tables, column_name must be qualified by a table name or, if used, table name alias.  (see Example 1).

Examples

Several of the examples below make use of tables in the HOCKEY schema, which is a schema defined in the TEST database.  The TEST database is created when the QuickStart process is run.  See QuickStart.
LinkExample Description
Example 1Create a view without column list
Example 2Create a view with column list

Example 1

create view without a column name list

Example 2

Create view using a column name list

 

 

  • No labels