NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
C-160
Examples—CREATE VIEW
protection view is partitioned, too, and each partition of the protection view is
registered in the same catalog as the corresponding partition of the table.
You cannot insert data in a protection view unless the view includes all the columns
of the underlying table that are defined with the NO DEFAULT option. In addition,
if the table underlying the view is an entry-sequenced table or a key-sequenced table
with a system-defined primary key, you cannot insert data in the view if the view
definition includes the system-defined primary key column in the WHERE clause.
A protection view inherits the AUDIT attribute of the underlying table. The
OWNER of a protection view is set to the owner of the underlying table.
The maximum number of protection views allowed on a table is approximately 180.
•
Shorthand views
A shorthand view is audited if all referenced tables and views are audited. A
shorthand view is nonaudited if all referenced tables and views are nonaudited. A
shorthand view has a mixed audit classification if some of the referenced tables or
views are audited and others are nonaudited, or if one of the referenced views has a
mixed audit classification.
The OWNER of a shorthand view is set to the process accessor ID of the creating
process.
A shorthand view that uses UNION cannot participate in a join. In addition, a
SELECT on such a view cannot specify a GROUP BY or HAVING clause or
include an aggregate function on any view column. A shorthand view based on an
inner or outer join cannot become an inner table of an outer join.
There is no limit on the number of shorthand views allowed on a table.
•
Grouped views
A grouped view is a view defined with a SELECT that contains a GROUP BY or
HAVING clause that is not in a subquery, contains an aggregate function in the
select list, or contains another grouped view in the FROM clause.
A grouped view cannot be joined with any other table or view. A query on a grouped
view cannot contain a GROUP BY or HAVING clause, nor can it specify an
aggregate function on any columns of the grouped view.
Examples—CREATE VIEW
•
The following example creates a shorthand view that includes part numbers and
supplier numbers for parts in which fewer than ten are in stock:
CREATE VIEW GETPARTS (PNUM, SNUM)
AS SELECT P.PARTNUM, SUPPNUM FROM PARTLOC P, PARTSUPP S
WHERE P.PARTNUM = S.PARTNUM AND QTY_ON_HAND < 10
CATALOG $VOL1.INVENT;