15.00 - NEW VARIANT_TYPE - Teradata Database

NEW VARIANT_TYPE

Purpose  

Constructs a new instance of a dynamic or VARIANT_TYPE UDT and defines the run time composition of the UDT.

Syntax  

where

Syntax element …	Specifies …
expression	any valid SQL expression; however, the following restrictions apply: expression cannot contain a dynamic UDT expression. Nesting of dynamic UDT expressions is not allowed. the first expression (that is, the first attribute of the dynamic UDT) cannot be a LOB, UDT, or LOB-UDT expression.
alias_name	a name representing the expression or column reference which corresponds to an attribute of the dynamic UDT. When provided, alias_name is used as the name of the attribute. You must provide an alias name for any expression that is not a column reference. You cannot assign the same alias name to more than one attribute of the dynamic UDT. Also, you cannot specify an alias name that is the same as a column name if that column name is already used as an attribute name in the dynamic UDT.
table_name	the name of the table in which the column being referenced is stored.
column_name	the name of the column being referenced. If you do not provide an alias name, the column name is used as the name of the corresponding attribute in the dynamic UDT. The same column name cannot be used as an attribute name for more than one attribute of the dynamic UDT. If a column has the same name as an alias name, the column name cannot be used as an attribute name.

ANSI Compliance

This is a Teradata extension to the ANSI SQL:2011 standard.

Usage Notes  

You can use the NEW VARIANT_TYPE expression to define the runtime composition or internal attributes of a dynamic UDT. Each expression you pass into the NEW VARIANT_TYPE constructor corresponds to one attribute of the dynamic UDT. You can assign an alias name to represent each NEW VARIANT_TYPE expression parameter. The name of the attribute will be the alias name provided or the column name associated with the column reference if no alias is provided. This is summarized in the following table:

IF...	THEN the attribute name is...
alias_name is provided	alias_name
table_name.column_name is provided, but alias_name is not provided	column_name
an expression is provided that is not a column reference and alias_name is not provided	an error is returned.

Note that you must provide an alias name for all expressions that are not column references. In addition, the attribute names must be unique. Therefore, you must provide unique alias names and/or column references.

The data type of the attribute will be the result data type of the expression. The resultant value of the expression will become the value of the corresponding attribute.

Restrictions

You can use the NEW VARIANT_TYPE expression only to construct dynamic UDTs for use as input parameters to UDFs. To construct a new instance of other structured UDTs, use the NEW expression. For details, see “NEW” on page 1346.

UDFs support a maximum of 128 parameters. Therefore, you cannot use NEW VARIANT_TYPE to construct a dynamic UDT with more than 128 attributes.

The sum of the maximum sizes for all the attributes of the dynamic UDT must not exceed the maximum permissible column size as configured for the Teradata Database. Exceeding the maximum column size results in the following SQL error: “ERR_TEQRWOVRFLW _T("Row size or Sort Key size overflow.")”.

Example  

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)

Example  

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;

Related Topics

FOR more information on …	SEE …
dynamic UDTs	“VARIANT_TYPE UDT” in SQL Data Types and Literals.
constructing a new instance of a structured UDT that is not a dynamic UDT	“NEW” on page 1346.
writing UDFs which use input parameters of VARIANT_TYPE data type	SQL External Routine Programming