15.00 - Set Operators in View Definitions - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)
Last Update
2018-09-24

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;