602SQL Documentation Index  
<­DROP DOMAIN Statement UpSQL Statements DROP VIEW Statement­>

CREATE VIEW Statement 

statement_CREATE_VIEW ::= CREATE [ IF NOT EXISTS ] VIEW [scheme.]query_name [ ( column_name {, column_name }… ) ] 
    AS query_expression

Description

The CREATE VIEW statement creates a query that is stored in the database. The query_expression selects a set of records from one or more tables. The column names in the query result are specified by the query_expression and may be changed by specifying a named list in parenthesis before the AS keyword.

Only the text definition of the query is stored in the database. The result is generated from the actual data each time the query is opened.

The maximum length of the a query name is 31 characters. Each query must have a unique name in the schema context. If the specified query name already exists, the error sqlstate 40004 (KEY_DUPLICITY) occurs.

Queries that can be edited can be used in SQL UPDATE, INSERT and DELETE statements. They can also be used when constructing SELECT query expressions or in the SELECT INTO statement. Fixed queries can be opened in routines by calling the OPEN statement, rows in the result can be read by calling the FETCH statement and referenced in the UPDATE CURRENT OF and DELETE CURRENT OF statements. The query can be closed using the CLOSE statement.

602SQL Variations

Example:

CREATE VIEW Salary6000 AS
    SELECT *
    FROM Employed
    WHERE salary<6000
    ORDER BY surname
CREATE VIEW salary (Sum_salary, Dep_Name) AS
    SELECT SUM(Employed.salary),Departement.Name
    FROM Employed, Departement
    WHERE Departement.number=Employed.departement
    GROUP BY Employed.departement

See

<­DROP DOMAIN Statement UpSQL Statements DROP VIEW Statement­>