Example 1 - Advanced SQL Engine - Teradata Database

Consider the following table definition and data:

CREATE TABLE pRecords (pname CHAR(30),
                       pkey INTEGER);
SELECT * FROM pRecords;

The output from the SELECT statement is:

pname                                  pkey
------------------------------  -----------
Tom                                       6
Bob                                       5
Jane                                      4

The following is the SQL definition of a scalar UDF that calculates the factorial of an integer argument:

CREATE FUNCTION factorial (i INTEGER)
RETURNS INTEGER
SPECIFIC factorial
LANGUAGE C
NO SQL
PARAMETER STYLE TD_GENERAL
NOT DETERMINISTIC
RETURNS NULL ON NULL INPUT
EXTERNAL NAME 'ss!factorial!factorial.c!F!fact'

The following query uses the scalar UDF expression to calculate the factorial of the pkey column + 1.

SELECT pname, factorial(pkey)+1 
FROM pRecords;

The output from the SELECT statement is:

pname                           (factorial(pkey)+1)
------------------------------  -------------------
Tom                                             721
Bob                                             121
Jane                                             25