NEW VARIANT_TYPE Function | UDT Expression Method | Teradata Vantage - 17.10 - NEW VARIANT_TYPE - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Operators and User-Defined Functions

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Administration
Programming Reference
Publication ID
B035-1210-171K
Language
English (United States)

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

ANSI Compliance

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

Syntax

NEW VARIANT_TYPE ( variant_type_spec [,...] )
variant_type_spec
{ expression AS alias_name |
  table_name.column_name [ AS alias_name ]
}

Syntax Elements

expression

Any valid SQL expression

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.