Icon CREATE VIEW

Creates a new view.

Syntax
CREATE VIEW <Name> AS
<View Definition>
[WITH CHECK OPTION|WITHOUT CHECK OPTION]
[DESCRIPTION <Description>]
[VERSION <VersionNumber>]
[ATTRIBUTES <CustomAttributes>]

Usage
Use this statement to create a new view. A view is a query expression that can act like a virtual table, and is useful when you want restrict which columns in a table (or tables) are visible to users. This can be accomplished by giving users (or roles) SELECT privileges on a view that only references a few select columns while not giving users (or roles) any SELECT privileges on the base table(s) that are referenced by the view.

The WITH CHECK OPTION clause is used with updateable views to specify whether INSERTS or UPDATES that would violate the WHERE clause will be permitted or not. If WITH CHECK OPTION is specified, then INSERTS or UPDATES that would violate the WHERE clause are not permitted.

Information Using the WITHOUT CHECK OPTION clause is the same as not specifying the WITH CHECK OPTION clause, and is present for compatibility with the ALTER VIEW syntax.

By default, ElevateDB always tries to make a view updateable if possible. The rules for updateability are the same as those for sensitive query result sets, and are discussed in detail in the Result Set Cursor Sensitivity topic.

Information It is possible to have a view be considered as updateable and still be read-only. Such is the case in situations where the current view SQL does fullfill the requirements for a sensitive result set, but the view references other views or derived tables that are not updateable. In such a case, the current view will inherit the updateable state of the referenced views or derived tables.

Any time the columns in any referenced base table or view change, ElevateDB will automatically reflect these changes in the view columns. You can always query this information via the ViewColumns Information schema table.

Examples
-- The following view selects the employee Name and
-- HireDate column from the Employees table.

CREATE VIEW "EmployeesList" AS
SELECT Name, HireDate
FROM Employees

-- The following view uses a derived table to retrieve
-- data.  It will be considered updateable, but will not
-- be updateable at runtime.

CREATE VIEW "DerivedSum" AS
SELECT *
FROM (SELECT CustNo, SUM(Orders.ItemsTotal) AS Total
      FROM Customer INNER JOIN Orders ON Orders.CustNo=Customer.CustNo
      GROUP BY CustNo) AS CustomerTotals
WHERE Total > 80000

Required Privileges
The current user must be granted the CREATE privilege on the current database in order to execute this statement. Please see the User Security topic for more information.

SQL 2003 Standard Deviations
This statement deviates from the SQL 2003 standard in the following ways:

DeviationDetails
Columns ListElevateDB does not support specifying a list of column names for the view. Instead, it always uses the column correlation names from the SELECT columns in the query expression to determine the names of the columns in the view.
RECURSIVEElevateDB does not support the RECURSIVE clause and recursive views. This means that you cannot reference the view being created within the view definition.
LOCAL or CASCADEDElevateDB does not support the LOCAL or CASCADED clauses in the WITH CHECK OPTION clause.
DESCRIPTIONThe DESCRIPTION clause is an ElevateDB extension.
VERSIONThe VERSION clause is an ElevateDB extension.
ATTRIBUTESThe ATTRIBUTES clause is an ElevateDB extension.
Image