NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
C-157
CREATE VIEW Statement
HEADING string | NO HEADING
specifies a default heading for the column. (See HEADING Clause on page H-1 for
more information.)
AS select-statement
specifies the columns for the view and sets the selection criteria that determines the
rows that make up the view.
select-statement cannot include a host variable, an INTO or ORDER BY
clause, or (except in a subquery) the BROWSE, STABLE, or REPEATABLE access
option.
A select-statement that defines a shorthand view can include subqueries, a
FROM clause with multiple table references, and WHERE, GROUP BY, HAVING,
and ALL clauses. However, if any column in the select list is a function or an
expression, select-statement must include a column list. In addition, the
DISTINCT clause is allowed only within a function; for example, the following
statement is allowed:
CREATE VIEW v ( c ) AS
SELECT COUNT (DISTINCT LAST_NAME) FROM EMPLOYEE;
However, the following statement is not allowed:
CREATE VIEW v ( c ) AS
SELECT DISTINCT LAST_NAME FROM EMPLOYEE;
A select-statement that defines a protection view must also meet the
following requirements:
•
The FROM clause can refer to one table (with a correlation name, if desired),
but cannot refer to another view.
•
The WHERE clause can refer only to columns in its select list.
•
The select list cannot include expressions or functions and cannot refer to
duplicate column names.
•
The statement cannot be combined with another SELECT statement using a
UNION operator and cannot include subqueries, the keyword DISTINCT, or the
GROUP BY or HAVING clause.
If select-statement includes a UNION operator, the view cannot be updated
and the view cannot participate in an inner or outer join.
If select-statement includes a LEFT JOIN operator, the view can be specified
only on the lefthand side of the first LEFT JOIN.
If ServerWare SMF is installed on your node, any table to which the view refers
must have either a virtual or direct name.