CREATE VIEW Statement

Creates a view on the database. Views are used to give a different perspective on the data even though it is not stored that way.

Syntax

CREATE [ OR REPLACE ] VIEW
… [ owner.]view-name [ ( column-name [ , … ] ) ]
… AS select-without-order-by
… [ WITH CHECK OPTION ]

Examples

Usage

A view can be created for another user by specifying the owner. You must have DBA authority to create a view for another user.

A view name can be used in place of a table name in SELECT, DELETE, UPDATE, and INSERT statements. Views, however, do not physically exist in the database as tables. They are derived each time they are used. The view is derived as the result of the SELECT statement specified in the CREATE VIEW statement. Table names used in a view should be qualified by the user ID of the table owner. Otherwise, a different user ID might not be able to find the table or might get the wrong table.

Specifying the OR REPLACE clause (CREATE OR REPLACE VIEW) creates a new view or replaces an existing view with the same name. Existing permissions are preserved when you use the OR REPLACE clause, but INSTEAD OF triggers on the view are dropped.

The columns in the view are given the names specified in the column name list. If the column name list is not specified, then the view columns are given names from the select list items. To use the names from the select list items, the items must be a simple column name or they must have an alias name specified (see SELECT Statement). You cannot add or drop IDENTIY/AUTOINCREMENT columns from a view.

Views can be updated unless the SELECT statement defining the view contains a GROUP BY clause, an aggregate function, or involves a UNION operation. An update to the view causes the underlying tables to be updated.

view-name—An identifier. The default owner is the current user ID.

column-name—The columns in the view are given the names specified in the column-name list. If the column name list is not specified, the view columns are given names from the select list items. To use the names from the select list items, each item must be a simple column name or have an alias name specified (see SELECT Statement).

AS—The SELECT statement on which the view is based must not contain an ORDER BY clause, a subquery in the SELECT list, or a TOP or FIRST qualification. It may have a GROUP BY clause and may be a UNION.

WITH CHECK OPTION—Rejects any updates and inserts to the view that do not meet the criteria of the views as defined by its SELECT statement. However, Sybase IQ currently ignores this option (it supports the syntax for compatibility reasons).

Side Effects
  • Automatic commit

Standards

  • SQL—Vendor extension to ISO/ANSI SQL grammar.

  • Sybase—Supported by Adaptive Server Enterprise.

Permissions

Must have RESOURCE authority and SELECT permission on the tables in the view definition.

Related reference
CREATE TABLE Statement
DROP Statement
SELECT Statement