Examples: WITH Statement Modifier - Teradata Database

Example: Common Table Expression in a Nonrecursive WITH Statement Modifier

A common table expression (CTE) in a nonrecursive WITH statement modifier can reference either preceding or subsequent CTEs defined in the WITH statement modifier, as long as the CTE does not indirectly reference itself. That is, circular references are not allowed.

Following is the table definition for this example.

CREATE TABLE orders (customer_id INTEGER, total_cost FLOAT);
INSERT INTO orders (43563, 734.12);
INSERT INTO orders (65758, 211.15);
INSERT INTO orders (23235, 1264.98);
INSERT INTO orders (43563, 583.23);
INSERT INTO orders (89786, 278.66);
INSERT INTO orders (13253, 401.97);
INSERT INTO orders (98765, 1042.23);
INSERT INTO orders (23235, 699.23);
INSERT INTO orders (43563, 935.35);
INSERT INTO orders (88354, 375.09);

This example of a nonrecursive WITH statement modifier includes a common table expression (CTE), specified as multiple_order_totals, that references the table multiple_orders, which is previously defined in the WITH clause.

WITH multiple_orders AS (
   SELECT customer_id, COUNT(*) AS order_count
   FROM orders
   GROUP BY customer_id
   HAVING COUNT(*) > 1
),
multiple_order_totals AS (
   SELECT customer_id, SUM(total_cost) AS total_spend
   FROM orders
   WHERE customer_id IN (SELECT customer_id FROM multiple_orders) 
   GROUP BY customer_id
)
SELECT * FROM multiple_order_totals
ORDER BY total_spend DESC;

The query returns this answer set:

This example of a nonrecursive WITH statement modifier includes a common table expression (CTE), specified as multiple_order_totals, that references the table multiple_orders, which is subsequently defined in the WITH clause.

WITH multiple_order_totals AS (
   SELECT customer_id, SUM(total_cost) AS total_spend
   FROM orders
   WHERE customer_id IN (SELECT customer_id FROM multiple_orders) 
   GROUP BY customer_id
),
multiple_orders AS (
   SELECT customer_id, COUNT(*) AS order_count
   FROM orders
   GROUP BY customer_id
   HAVING COUNT(*) > 1
)
SELECT * FROM multiple_order_totals
ORDER BY total_spend DESC;

The query returns this answer set:

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:

The data in the trains table is as follows:

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 UDFs

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;