Contents Index CREATE VARIABLE statement CREATE WRITEFILE statement

ASA SQL Reference
  SQL Statements

CREATE VIEW statement


Description 

Use this statement to create 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 VIEW
owner.]view-name [ ( column-name, ... ) ]
AS select-statement
WITH CHECK OPTION ]

Parameters 

view-name    The view-name is 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. In order 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). All items in the select list must have unique names.

AS clause    The SELECT statement on which the view is based must not have an ORDER BY clause on it. It may have a GROUP BY clause and may be a UNION. The SELECT statement must not refer to local temporary tables.

WITH CHECK OPTION clause    The WITH CHECK OPTION clause rejects any updates and inserts to the view that do not meet the criteria of the views as defined by its SELECT statement.

Usage 

The CREATE VIEW statement creates a view with the given name. You can create a view owned by 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.

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 table(s) to be updated.

Permissions 

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

Side effects 

Automatic commit.

See also 

DROP statement

CREATE TABLE statement

Standards and compatibility 
Example 

The following example creates a view showing information for male employees only. This view has the same column names as the base table.

CREATE VIEW male_employee
AS SELECT *
FROM Employee
WHERE Sex = 'M'

The following example creates a view showing employees and the departments they belong to.

CREATE VIEW emp_dept
AS SELECT emp_lname, emp_fname, dept_name
FROM Employee JOIN Department
ON Employee.dept_id = Department.dept_id

Contents Index CREATE VARIABLE statement CREATE WRITEFILE statement