Example: Variable Reference Table Function Called from a Derived Table - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
wgr1555383704548.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

This example shows a variable reference table function (see “Variable Mode Table Function Body” in Teradata Vantage™ - SQL External Routine Programming , B035-1147 ) equijoined to a derived table.

The derived table dt1 is created first as a subset of tbl1, then a column dt1.c1 for each row is passed to the table function. The resultant rows of the table function are combined with the selected rows of the derived table by means of the WHERE condition.

The relevant function definition is as follows:

    CREATE FUNCTION tudf1 (FLOAT, INTEGER, INTEGER)
      RETURN TABLE (
       c1 DECIMAL,
       c2 INTEGER,
       c3 INTEGER) 
      … ;

The SELECT request that uses this table function is as follows.

    SELECT dt1.c1, tf2.c2, tf2.c3 
    FROM (SELECT c1, c2 
          FROM tbl1 
          WHERE c1 < 500) AS dt1,
           TABLE (tudf1(45.6, 193, dt1.c1)) AS tf2 (nc1, nc2, nc3)
           WHERE dt1.c1 = tf2.nc1);