16.20 - Example - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Operators and User-Defined Functions

Product
Advanced SQL Engine
Teradata Database
Release Number
16.20
Release Date
April 2020
Content Type
Administration
Programming Reference
Publication ID
B035-1210-162K
Language
English (United States)

The following NEW VARIANT_TYPE expression creates a dynamic UDT with a single attribute named weight:

NEW VARIANT_TYPE (Table1.a AS weight)

In the next example, the NEW VARIANT_TYPE expression creates a dynamic UDT with a single attribute named height. In this example, no alias name is specified; therefore, the column name is used as the attribute name.

NEW VARIANT_TYPE (Table1.height)

In the next example, the first attribute is named height based on the column name. However, the second attribute is also named height based on the specified alias name. This is not allowed since attribute names must be unique; therefore, the Teradata Database returns the error, “ERRTEQDUPLATTRNAME - "Duplicate attribute names in the attribute list. %VSTR", being returned to the user.”

NEW VARIANT_TYPE (Table1.height, Table1.a AS height)

This example shows a user-defined aggregate function with an input parameter named parameter_1 declared as VARIANT_TYPE data type. The SELECT statement calls the new function using the NEW VARIANT_TYPE expression to create a dynamic UDT with two attributes named a and b.

CREATE TYPE INTEGERUDT AS INTEGER FINAL;
CREATE FUNCTION udf_agch002002dynudt (parameter_1  VARIANT_TYPE)
RETURNS INTEGERUDT CLASS AGGREGATE (4) LANGUAGE C  NO SQL
EXTERNAL NAME   'CS!udf_agch002002dynudt!udf_agch002002dynudt.c'
PARAMETER STYLE SQL;
SELECT udf_agch002002dynudt(NEW VARIANT_TYPE (Tbl1.a AS a,
                                             (Tbl1.b + Tbl1.c) AS b))
FROM Tbl1;