17.05 - Examples: External UDFs - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

The following examples show ways to specify scalar or table external UDFs in a WITH modifier.

In this request, the input to the external UDF is recursive, and the UDF is joined with a recursive query.

     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 query.

     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 query.

     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 query.

     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 a Recursive Query

This example invokes the SQL UDF value_expression in the WHERE clause of the recursive query.

     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;