15.00 - Set Operators in View Definitions - Teradata Database

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:

UPDATE, DELETE, and INSERT are not applicable. The following example does not work:

   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.

WITH CHECK OPTION is not applicable. The following example does not work:

   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.

Column level privileges cannot be granted. The following example does not work:

   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.

A view definition that uses set operators cannot specify an ORDER BY clause, but a SELECT statement applied on the view can use ORDER BY. For details, see “GROUP BY and ORDER BY Clauses” on page 1160.

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;