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

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1144-171K
Language
English (United States)

This example shows a variable reference table function (see 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);