Set Operators with Derived Tables - Teradata VantageCloud Lake

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 ;

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;