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;