Example : Multiple Seed and Recursive Statements
This example shows a recursive query that uses multiple seed and recursive statements.
Consider these two tables:
CREATE TABLE planes (
depart VARCHAR(40),
arrive VARCHAR(40),
carrier VARCHAR(40),
cost DECIMAL(5,0));
CREATE TABLE trains (
depart VARCHAR(40),
arrive VARCHAR(40),
cost DECIMAL(5,0));
The data in the planes table is as follows:
|
Depart |
Arrive |
Carrier |
Cost |
|
Paris |
New York |
AA |
199 |
|
Paris |
London |
AA |
99 |
|
London |
New York |
AA |
199 |
|
New York |
Mexico City |
UA |
99 |
|
Mexico City |
New York |
UA |
99 |
|
Paris |
Mexico City |
AF |
299 |
|
New York |
London |
AA |
199 |
|
New York |
Tokyo |
JAL |
999 |
|
Mexico City |
Tokyo |
JAL |
999 |
|
Tokyo |
New York |
JAL |
999 |
The data in the trains table is as follows:
|
Depart |
Arrive |
Cost |
|
Paris |
London |
99 |
|
London |
Paris |
99 |
|
Paris |
Milan |
199 |
|
London |
Milan |
199 |
|
Milan |
Paris |
199 |
|
Milan |
Rome |
49 |
|
Rome |
Florence |
49 |
The following query uses two seed statements and two recursive statements to return all cities reachable from Paris by train or plane.
WITH RECURSIVE temp_table (depart, arrive, carrier, depth) AS (
SELECT p_root.depart, p_root.arrive, p_root.carrier, 0 AS depth
FROM planes p_root
WHERE p_root.depart = 'Paris'
UNION ALL
SELECT t_root.depart, t_root.arrive, 'EuroRail', 0 AS depth
FROM trains t_root
WHERE t_root.depart = 'Paris'
UNION ALL
SELECT direct.depart, indirect.arrive, indirect.carrier, direct.depth+1
FROM temp_table AS direct, planes AS indirect
WHERE direct.arrive = indirect.depart
AND indirect.arrive <> 'Paris'
AND direct.depth <= 4
UNION ALL
SELECT direct.depart, indirect.arrive, 'EuroRail', direct.depth+1
FROM temp_table AS direct, trains AS indirect
WHERE direct.arrive = indirect.depart
AND indirect.arrive <> 'Paris'
AND direct.depth <= 4)
)
SELECT DISTINCT arrive (TITLE 'Destinations Reachable From Paris')
FROM temp_table;
The result set for this recursive query is as follows:
Destinations Reachable From Paris
----------------------------------------
Florence
London
Mexico City
Milan
New York
Rome
Tokyo
Example : WITH Statement Modifier Result Set as Input to a Table Function
Suppose you have created these tables and table function.
CREATE TABLE t1 (
a1 INTEGER,
b1 INTEGER);
CREATE TABLE t2 (
a2 INTEGER,
b2 INTEGER);
CREATE 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';
Use the temporary result set derived from the subquery in the WITH statement modifier as input to table function add2int in the FROM clause.
WITH dt(a,b) AS (
SELECT a1, b1
FROM t1)
SELECT addend1, addend2, mysum
FROM dt, TABLE (add2int(dt.a, dt.b)) AS tf
ORDER BY 1,2,3;
Example : Specifying a Dynamic UDT in a Recursive Query
The following example shows how you can use dynamic UDTs in a recursive query.
WITH MyDerived(u_sal) AS (
SELECT NEW MP_STRUCTURED_INT(salary, '1', '1') AS u_sal
FROM employee)
SELECT udf_aggr_avg_mp_struc(NEW VARIANT_TYPE(1 AS dummy,
u_sal AS x) )
FROM MyDerived;
*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.
udf_aggr_avg_mp_struc(NEW VARIANT_TYPE (dummy, x))
--------------------------------------------------
33438
Example : External UDF Examples
The following examples all show various simple ways to specify scalar or table external UDFs in a WITH or WITH RECURSIVE statement modifier.
In this request, the input to the external UDF is recursive, and the UDF is joined with a recursive table.
WITH RECURSIVE dt(a,b,c,d) AS (
SELECT a1, b1,a1-b1,0
FROM t1
UNION ALL
SELECT addend1, addend2, mysum,d+1
FROM dt,table (add2int(dt.a,dt.b)) AS tf
WHERE d < 2
)
SELECT *
FROM dt;
In this statement, the input to the external UDF is not recursive, and the UDF is joined with a recursive table.
WITH RECURSIVE dt(a,b,c,d) AS (
SELECT a1, b1,a1-b1,0
FROM t1
UNION ALL
SELECT addend1, addend2, mysum,d+1
FROM dt,table (add2int(t1.a1,t1.b1)) AS tf
WHERE d < 2
)
SELECT *
FROM dt;
In this statement, the input to the external UDF is recursive, and the UDF is not joined with a recursive table.
WITH RECURSIVE dt(a,b,c,d) AS (
SELECT a1, b1,a1-b1,0
FROM t1
UNION ALL
SELECT addend1, r.b1, mysum, 1 AS d
FROM table (add2int(dt.a,dt.b)) tf, t1 r
WHERE d < 1
AND tf.addend1=t1.a1
)
SELECT *
FROM dt;
In this statement, the input to the external UDF is not recursive, and the UDF is not joined with a recursive table.
WITH dt(a,b,c) AS (
SELECT a1, b1 ,a1-b1
FROM t1
UNION ALL
SELECT addend1, addend2, mysum
FROM table (add2int(t1.a1, t1.b1)) tf
)
SELECT *
FROM dt;
Example : Invoking an SQL UDF in the WITH RECURSIVE Statement Modifier
This example invokes the SQL UDF value_expression in the WHERE clause of the WITH RECURSIVE statement modifier.
WITH RECURSIVE temp_table (employee_number) AS (
SELECT root.employee_number
FROM employee AS root
WHERE root.manager_employee_number = 801
AND test.value_expression(dept_no, 0) = 25;
UNION ALL
SELECT indirect.employee_number
FROM temp_table AS direct, employee AS indirect
WHERE direct.employee_number = indirect.manager_employee_number
AND test.value_expression(2,3) = 5
)
SELECT *
FROM temp_table
ORDER BY employee_number;
For more information on recursive queries and views, see: