Example: Local Ordering of Input Parameters to a Table Function - Teradata VantageCloud Lake

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

The query in this example selects all columns from add2int, which specifies that its input, dt, be value-ordered on each AMP by dt.x1. The specified local ordering may not be relevant to the add2int function and is only used for illustration.

The expected outcome of the query is that the rows on each AMP 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);

     CREATE 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';
     WITH dt(x1,y1) AS (SELECT a1,b1
                        FROM t1)

     SELECT *
     FROM TABLE (add2int(dt.x1,dt.y1)
     LOCAL ORDER BY x1) AS tf;