Example: Local Ordering of Input Parameters to a Table Function - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantage™

The following example illustrates 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 of them 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. Note that the specified local ordering might 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;