CREATE VIEW
Function
The CREATE VIEW statement is used to create a view derived from one or more base tables or other views.
Syntax
CREATE VIEW view_specification [(column_identifier [,])] AS QUERY expression
where
view_specification | The expected format is: schema identifier.table identifier. The default schema identifier is assumed if only a table identifier is specified. All views are placed in the CONNX catalog of CONNXDB. |
column_identifier | Column identifier of a view |
query_expression | |
Description
The CREATE VIEW statement is used to specify a viewed table, also called view. A view is a virtual table and therefore, has no physical representation. Values are conceptually derived from base tables as the need arises. If a schema identifier is given in the table specification, then the table identifier will thus be explicitly qualified, otherwise the current default schema identifier will be used.
The column identifier list specifies the number and order in which the columns will appear in the view. The number of column identifiers must equal the number of derived columns defined in the query specification. The nth column identifier represents the nth derived column and assumes its data type. Furthermore, two columns within the column identifier list may not be called the same.
If no column identifier list is specified, then the columns of the view are identified by the unqualified derived column labels of the query specification. If there is no label for a particular derived column, then the complete column identifier list must be specified.
A view is called a "joined view" if more than one table has been specified or a joined view has been referenced in the FROM clause.
A view is called a "grouped view" if the view is derived from a grouped query specification.
A view is called a "read-only view" if the view is either grouped or joined or at least one of the derived columns does not have a label.
Only after successful execution of the statement is the view generally available. During execution the view description is stored in the catalog.
Limitations
The DBA can execute this statement for all users. All other users can use this statement only in a schema owned by the user. Creation of a view must include the following limitations:
The table specification must be unique within an SQL environment, at runtime.The query specification may not reference host variables.
The number of column identifiers specified in the desired column list must be identical to the number of derived columns given in the query specification.
If no column identifier list is specified, all derived columns must have labels.
The query specification may not reference the view which is the subject of the CREATE VIEW statement.
A view can not be updated when:
it is a joined or grouped view, as described above
a derived column is a literal (CREATE VIEW xyz AS SELECT col1, 'London' FROM table1)
a derived column is an expression (CREATE VIEW xyz AS SELECT col1+3 FROM table1)
Warning:
This statement is not subject to transaction logic. An implicit COMMIT will be performed after successful execution of this statement. If an error is detected during execution of this statement, an implicit ROLLBACK will be performed. Therefore, before executing this statement, it is strongly recommended to complete any open transaction containing INSERT, UPDATE and/or DELETE statements by issuing an explicit COMMIT or ROLLBACK statement.
ANSI Specifics
Within the ANSI concept the CREATE VIEW statement must be embedded in a CREATE SCHEMA statement. The SCHEMA as defined in ANSI is not fully supported by Adabas SQL Gateway Embedded SQL.
Examples
The following example creates a view named United States:
CREATE VIEW united_states
AS SELECT * FROM persons
WHERE country = 'USA' ;
Once the above view is created, it can be used to access information as if it were a normal table:
SELECT person_id
FROM united_states
WHERE united_states.city = 'PHILADELPHIA'