Ordering Input Arguments to Table UDFs - Analytics Database - 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 the information about SELECT in Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.