15.00 - Set Operators With Derived Tables - 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 With Derived Tables

Derived tables support set operators, as demonstrated in the following example:

Example  

   SELECT x1 
   FROM table_1,
   (SELECT x2 
   FROM table_2
   UNION
   SELECT x3 
   FROM table_3
   ) derived_table;
 
   SELECT x1,y1 
   FROM table_1,
   (SELECT * 
   FROM table_2) derived_table(column_1, column_2)
   WHERE column_2 = 1 ;

Restrictions

You cannot use the HASH BY or LOCAL ORDER BY clauses in derived tables with set operators. The following example returns an error.

Example  

The following table function "add2int" takes two integers as input and returns the two integers and their summation.

   CREATE TABLE t1 (a1 INTEGER, b1 INTEGER);
   CREATE TABLE t2 (a2 INTEGER, b2 INTEGER);
 
   REPLACE FUNCTION add2int
      (a INTEGER,
       b INTEGER)
   RETURNS TABLE
      (addend1 INTEGER,
       addend2 INTEGER,
       mysum INTEGER)
   SPECIFIC add2int
   LANGUAGE C
   NO SQL
   PARAMETER STYLE SQL
   NOT DETERMINISTIC
   CALLED ON NULL INPUT
   EXTERNAL NAME 'CS!add3int!add2int.c';
 
   /* Query Q1 */
   WITH dt(a1, b1) AS
   ( SELECT a1, b1
     FROM t1
     UNION ALL
     SELECT a2, b2
     FROM t2
   )
   SELECT * 
   FROM TABLE (add2int(dt.a1, dt.b1) 
   HASH BY b1 
   LOCAL ORDER BY b1) tf;