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: |
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
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 |
|
writing UDFs which use input parameters of VARIANT_TYPE data type |
SQL External Routine Programming |