Set Operators in View Definitions
Set operators are permitted within view definitions.
For example, the following REPLACE VIEW statement uses UNION within a view definition:
REPLACE VIEW view_1 AS
SELECT x1,y1
FROM table_1
UNION
SELECT x2,y2
FROM table_2;
Support for the GROUP BY Clause
GROUP BY can be used within views with set operators. For details, see “GROUP BY and ORDER BY Clauses” on page 1160.
Restrictions
The following limitations apply to view definitions that specify set operators:
REPLACE VIEW V AS
SELECT X
FROM TABLE_1
UNION
SELECT Y FROM
TABLE_1;
UPDATE V
SET X=0;
An attempt to perform this sequence of statements produces the following error message:
***Failure 3823 VIEW 'v' may not be used for Help Index/
Constraint/Statistics, Update, Delete or Insert.
REPLACE VIEW ERRV( c ) AS
SELECT *
FROM TABLE_1
UNION
SELECT *
FROM TABLE_2
WHERE TABLE_2.X=2 WITH CHECK OPTION;
An attempt to perform this statement causes the following error message:
***Failure 3847 Illegal use of a WITH clause.
GRANT UPDATE ( c ) ON TABLE_VIEW TO USER_NAME;
An attempt to perform this statement causes the following error message:
***Failure 3499: GRANT cannot be used on views with set operators.
Examples
The following examples provide correct uses of set operators within view definitions.
Example
REPLACE VIEW v AS
SELECT x1
FROM TABLE_1
UNION
SELECT x2
FROM TABLE_2
UNION
SELECT x3
FROM TABLE_3;
SELECT * FROM v;
Example
REPLACE VIEW view_2 AS
SELECT *
FROM view_1
UNION
SELECT *
FROM table_3
UNION
SELECT *
FROM table_4;
SELECT *
FROM view_2
ORDER BY 1,2;
Example
REPLACE VIEW v AS
SELECT x1
FROM table_1
WHERE x1 IN
(SELECT x2
FROM table_2
UNION
SELECT x3
FROM table_3
);
SELECT * FROM v;