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);