Usage Notes - Advanced SQL Engine - Teradata Database

SQL Operators and User-Defined Functions

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2023-04-27
dita:mapPath
qqu1556127655717.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1210
lifecycle
previous
Product Category
Teradata Vantage™

When Teradata Database evaluates a table UDF expression, it invokes the table function which returns a table a row at a time in a loop to the SELECT statement. The function can produce the rows of a table from the input arguments passed to it or by reading an external file or message queue.

A table function can have 128 input parameters. The rules that apply to the arguments in a table function call are the same as those that apply to a scalar function call. See UDF Arguments.

Table UDFs do not have return values. The columns in the result rows that they produce are returned as output parameters.

The output parameters of a table function are defined by the RETURNS TABLE clause of the CREATE FUNCTION statement. The number of output parameters is limited by the maximum number of columns that can be defined for a regular table.

The number and data types of the output parameters can be specified statically in the CREATE FUNCTION statement or dynamically at runtime in the SELECT statement that invokes the table function.

Table Functions and Table Operators cannot execute against fallback data when an AMP is Down. Once the AMP is returned to service the query can complete as normal.

Example

In this example, the extract_field table UDF is used to extract the customer ID, store number, and item ID from the pending_data column of the raw_cust table.

The raw_cust table is defined as:

CREATE SET TABLE raw_cust ,NO FALLBACK ,
   NO BEFORE JOURNAL,
   NO AFTER JOURNAL,
   CHECKSUM = DEFAULT
   (
    region INTEGER,
    pending_data VARCHAR(32000) CHARACTER SET LATIN NOT CASESPECIFIC)
PRIMARY INDEX (region);

The pending_data text field is a string of numbers with the format:

store number, entries:customer ID, item ID , ...; repeat;

where:

  • store number is the store that sold these items to customers.
  • entries is the number of items that were sold.
  • customer ID, item ID represent the item each customer bought. customer ID, item ID is repeated entries times ending with a semi-colon ';'.
  • The above sequence can be repeated.

The following shows sample data from the raw_cust table:

  region  pending_data
--------  ---------------------------------------------------------
       2  7,2:879,3788,879,4500;08,2:500,9056,390,9004;
       1  25,3:9005,3789,9004,4907,398,9004;36,2:738,9387,738,9550;
       1  25,2:9005,7896,9004,7839;36,1:737,9387;

The following shows the SQL definition of the extract_field table UDF:

CREATE FUNCTION extract_field (Text VARCHAR(32000),
                               From_Store INTEGER)
RETURNS TABLE (Customer_ID INTEGER,
               Store_ID INTEGER,
               Item_ID INTEGER)
LANGUAGE C
NO SQL
PARAMETER STYLE SQL
EXTERNAL NAME extract_field;

The following query extracts and displays the customers and the items they bought from store 25 in region 1.

SELECT DISTINCT cust.Customer_ID, cust.Item_ID
FROM raw_cust,
TABLE (extract_field(raw_cust.pending_data, 25))
AS cust
WHERE raw_cust.region = 1;

The output from the SELECT statement is similar to:

Customer_ID       Item_ID
------------  -----------
        9005         3789
        9004         4907
         398         9004
        9005         7896
        9004         7839