Example - Advanced SQL Engine - Teradata Database

SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
vjt1596846980081.ditamap
dita:ditavalPath
vjt1596846980081.ditaval
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata Vantageā„¢

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;