SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
C-167
CREATE VIEW Statement
[ ( new-name [ , new-name ] ... ) ]
specifies names for the columns of the view and, optionally, headings for the
columns. If you do not specify this clause, columns in the view have the same
names as the columns in the select list of select-statement.
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.
new-name is:
new-column-name [ HEADING string | NO HEADING ]
new-column-name
is an SQL identifier that is not a reserved word and that is unique among column
names for the view. Column names in the list must match one-for-one with
columns in the select-list of the AS select-statement clause.
HEADING string | NO HEADING
specifies a default heading for the column. (For more information, see HEADING
Clause on page H-1.)
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, this statement is
allowed:
CREATE VIEW v ( c ) AS
SELECT COUNT (DISTINCT LAST_NAME) FROM EMPLOYEE;
However, this 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 these
requirements: