CREATE VIEW Statement

Quick Links:

Syntax

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

Parameters

(back to top)

OR REPLACE – replaces an existing view with the same name. Existing permissions are preserved , but INSTEAD OF triggers on the view are dropped.

column-name – specifies the names of the columns in the view. 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).

view-name – The default owner of a view is the current user ID. 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.

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, SAP Sybase IQ currently ignores this option (it supports the syntax for compatibility reasons).

Examples

(back to top)

Example 1 – creates a view showing all information for male employees only. This view has the same column names as the base table:
```
CREATE VIEW male_employee
AS SELECT *
FROM Employees
WHERE Sex = 'M'
```

Example 2 – creates a view showing employees and the departments to which they belong:

CREATE VIEW emp_dept
AS SELECT Surname, GivenName, DepartmentName
FROM Employees JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID

Usage

(back to top)

You cannot add or drop IDENTIFY or 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.

Side Effects

Automatic commit

Standards

(back to top)

SQL—Vendor extension to ISO/ANSI SQL grammar.
SAP Sybase Database product—Supported by Adaptive Server.

Permissions

(back to top)

View to be owned by self – Requires CREATE VIEW system privilege. Also requires one of:

SELECT ANY TABLE system privilege.
SELECT object permission on the underlying tables of the view.

View to be owned by any user – Requires one of:

CREATE ANY VIEW system privilege.
CREATE ANY OBJECT system privilege.
Also requires one of:
- SELECT ANY TABLE system privilege.
- SELECT object privilege on the underlying tables of the view.

Materialized view to be owned by self – Requires CREATE MATERIALIZED VIEW system privilege. Also requires one of:

CREATE ANY OBJECT system privilege.
CREATE privilege on the dbspace where the materialized view is being created.
Also requires one of:
- SELECT ANY TABLE system privilege.
- SELECT privilege on the underlying tables of the materialized view.

Materialized view to be owned by any user – Requires one of:

CREATE ANY MATERIALIZED VIEW system privilege.
CREATE ANY OBJECT system privilege.
Also requires one of:
- CREATE ANY OBJECT system privilege.
- CREATE privilege on the dbspace where the materialized view is being created.
And also requires one of:
- SELECT ANY TABLE system privilege.
- SELECT privilege on the underlying tables of the materialized view.