Example: CTE Result Set as Input to a Table Function - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantage™

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;