Neoview SQL Reference Manual (R2.4 SP2)

CREATE VIEW Statement
• “Syntax Description of CREATE VIEW”
• “Considerations for CREATE VIEW”
• “Examples of CREATE VIEW”
The CREATE VIEW statement creates a Neoview SQL view. See “Views” (page 299).
CREATE VIEW view
[(column-name ] [,column-name ...)]
AS query-expr [order-by-clause]
[WITH [CASCADED] CHECK OPTION]
Syntax Description of CREATE VIEW
view
specifies the name of the view. See “Database Object Names” (page 230).
(column-name [,column-name ]...)
specifies names for the columns of the view. Column names in the list must match one-for-one
with columns in the table specified by query-expr.
If you omit this clause, columns in the view have the same names as the corresponding
columns in query-expr. You must specify this clause if any two columns in the table
specified by query-expr have the same name or if any column of that table does not have
a name. For example, this query expression SELECT MAX(salary), AVG(salary) AS
average_salary FROM employee the first column does not have a name.
No two columns of the view can have the same name; if a view refers to more than one table
and the select list refers to columns from different tables with the same name, you must
specify new names for columns that would otherwise have duplicate names.
AS query-expr
specifies the columns for the view and sets the selection criteria that determines the rows
that make up the view. For information about character string literals, see “Character String
Literals” (page 261). For more information about character sets, see Neoview Character Sets
Administrator's Guide. For the syntax and syntax description of query-expr, see “SELECT
Statement” (page 163). The CREATE VIEW statement provides this restriction with regard
to the query-expr syntax: [ANY N], [FIRST N] select list items are not allowed in a view.
order-by-clause
specifies the order in which to sort the rows of the final result table. For the syntax and syntax
description of the order-by-clause, see “SELECT Statement” (page 163). The CREATE
VIEW statement restricts the order-by-clause with regard to the access-clause and
mode-clause. The access-mode and mode-clause cannot follow the order-by-clause.
WITH [CASCADED] CHECK OPTION
specifies that no row can be inserted or updated in the database through the view unless the
row satisfies the view definition—that is, the search condition in the WHERE clause of the
query expression must evaluate to true for any row that is inserted or updated. This option
is only allowed for updatable views.
If you omit this option, a newly inserted row or an updated row need not satisfy the view
definition, which means that such a row can be inserted or updated in the table but does not
appear in the view. This check is performed each time a row is inserted or updated.
WITH CHECK OPTION does not affect the query expression; rows must always satisfy the
view definition. CASCADED is an optional keyword; WITH CHECK OPTION has the same
effect, with and without this option.
CREATE VIEW Statement 101