Example: HASH BY and LOCAL ORDER BY Clauses in the Same Statement - Analytics Database - Teradata Vantage

This example shows the use of the HASH BY and LOCAL ORDER BY clauses for sorting the input to a table UDF.

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

Consider this table definition:

     CREATE TABLE tempdata (
       tid INTEGER,
       tts TIMESTAMP,
       x   INTEGER,
       y   INTEGER);

Suppose the data in tempdata looks like this:

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

   CREATE FUNCTION char_from_rows(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;

The following statement invokes the char_from_rows 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 (char_from_rows(wq.tID1, wq.tTS1, wq.x1, wq.y1)
   HASH BY tID1 LOCAL ORDER BY tTS1) AS 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