Example: Hash Ordering Input Parameters to a Table Function - Advanced SQL Engine - Teradata Database

SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
qtb1554762060450.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata Vantage™

This example shows the use of the HASH BY clause 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, is hashed by dt.y1. The specified hashing might not be relevant to the add2int function and is only used for illustration.

The expected processing of the query is that dt is first spooled and then hashed by y1 among the AMPs. The final hashed 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)
     HASH BY y1) AS aa;