Restrictions on Views

Restrictions on Views
The ISO standard imposes several important restrictions on the creation and use of views,
although there is considerable variation among dialects.
n If a column in the view is based on an aggregate function, then the column may appear
only in SELECT and ORDER BY clauses of queries that access the view. In particular,
such a column may not be used in a WHERE clause and may not be an argument to
an aggregate function in any query based on the view. For example, consider the view
StaffPropCnt of Example 6.5, which has a column cnt based on the aggregate function
COUNT. The following query would fail:
SELECT COUNT(cnt)
FROM StaffPropCnt;
because we are using an aggregate function on the column cnt, which is itself based on
an aggregate function. Similarly, the following query would also fail:
SELECT *
FROM StaffPropCnt
WHERE cnt > 2;
because we are using the view column, cnt, derived from an aggregate function in a
WHERE clause.
n A grouped view may never be joined with a base table or a view. For example, the
StaffPropCnt view is a grouped view, so that any attempt to join this view with another
table or view fails.