Example: Ordering Input Parameters to a Table Function - Teradata Vantage

The following example shows the use of the HASH BY and LOCAL ORDER BY clauses for a table UDF. Table function add2int takes two integer values as input and returns them and their sum.

Query Q1 selects all columns from add2int, which requests its input, dt, to be hashed by dt.y1 and value-ordered on each AMP by dt.x1. The specified hashing and local ordering may not be relevant to the add2int function and is only used for illustration.

The expected outcome of Q1 is that dt is first spooled and then hashed by y1 among the AMPs. On each AMP, the rows are sorted by the value of x1. The final hashed and sorted spool is used as the input to add2int.

     CREATE TABLE t1 (
       a1 INTEGER,
       b1 INTEGER);
     CREATE TABLE t2 (
       a2 INTEGER,
       b2 INTEGER);
     REPLACE FUNCTION add2int (
       a INTEGER, 
       b INTEGER)
     RETURNS TABLE (
       addend1 INTEGER, 
       addend2 INTEGER, 
       mysum INTEGER)
     SPECIFIC add2int  
     LANGUAGE C  
     NO SQL  
     PARAMETER STYLE SQL
     NOT DETERMINISTIC
     CALLED ON NULL INPUT
     EXTERNAL NAME 'CS!add3int!add2int.c';
/* Query Q1 */
     WITH dt(x1,y1) AS (SELECT a1,b1
                        FROM t1)
     SELECT *  
     FROM TABLE (add2int(dt.x1,dt.y1) 
     HASH BY y1 
     LOCAL ORDER BY x1) AS tf;