17.05 - Example - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

The following table function "add2int" takes two integers as input and returns the two integers and their summation.

   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(a1, b1) AS
   ( SELECT a1, b1
     FROM t1
     UNION ALL
     SELECT a2, b2
     FROM t2
   )
   SELECT * 
   FROM TABLE (add2int(dt.a1, dt.b1) 
   HASH BY b1 
   LOCAL ORDER BY b1) tf;