Ordering Input Arguments to Table UDFs - Advanced SQL Engine - Teradata Database

SQL External Routine Programming

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

Some applications need to enforce the ordering of input to table UDFs. Rather than perform any sort processing of input arguments in the application or table UDF, you can use the HASH BY and LOCAL ORDER BY clauses when you invoke the table UDF in the FROM clause of the SELECT statement. The scope of input includes derived tables, views, and WITH objects.

Consider the following table definition:

CREATE TABLE
   tempData(tID INTEGER, tTS TIMESTAMP, x INTEGER, y INTEGER);

Suppose the data in the table looks something like this:

INSERT INTO tempData
   VALUES (1001, TIMESTAMP '2008-02-03 14:33:15', 10, 11);
INSERT INTO tempData
   VALUES (1001, TIMESTAMP '2008-02-03 14:44:20', 20, 24);
INSERT INTO tempData
   VALUES (1001, TIMESTAMP '2008-02-03 14:59:08', 31, 30);
INSERT INTO tempData
   VALUES (1002, TIMESTAMP '2008-02-04 11:02:19', 10, 11);
INSERT INTO tempData
   VALUES (1002, TIMESTAMP '2008-02-04 11:33:04', 22, 18);
INSERT INTO tempData
   VALUES (1002, TIMESTAMP '2008-02-04 11:48:27', 29, 29);

Now consider a table UDF called CharFromRows that produces a text string that represents all of the timestamp, x, and y values in rows that have the same value for the tID column. Furthermore, the values in the text string are ordered by timestamp. Here is the definition of the table UDF:

CREATE FUNCTION CharFromRows(tID INTEGER,
                             tTS TIMESTAMP,
                             x INTEGER,
                             y INTEGER)
RETURNS TABLE(outID INTEGER, outCHAR VARCHAR(64000))
LANGUAGE C
NO SQL
EXTERNAL NAME 'CS!charfromrows!udfsrc/charfromrows.c'
PARAMETER STYLE SQL;

Here is a query that invokes the CharFromRows table UDF, using a nonrecursive WITH clause to hash the input by tID and value order the input on each AMP by tTS:

WITH wq (tID1, tTS1, x1, y1) AS
   (SELECT tID, tTS, x, y FROM tempData)
SELECT *
FROM TABLE (CharFromRows(wq.tID1, wq.tTS1, wq.x1, wq.y1)
   HASH BY tID1 LOCAL ORDER BY tTS1) tudf;

The output looks like this:

outID outCHAR
----- ---------------------------------------------------------------------
1001  2008-02-03 14:33:1510112008-02-03 14:44:2020242008-02-03 14:59:083130
1002  2008-02-04 11:02:1910112008-02-04 11:33:0422182008-02-04 11:48:272929

For details on the HASH BY and LOCAL ORDER BY clauses, see "FROM TABLE" in Teradata Vantage™ - SQL Data Definition Language Detailed Topics , B035-1184 .