UDT Expression - Teradata Database

UDT Expression

Purpose  

Returns a distinct or structured UDT data type.

Syntax  

where:

Syntax element …	Specifies …
database_name	an optional qualifier for the column_name.
table_name	an optional qualifier for the column_name.
column_name	the name of a distinct or structured UDT column.
udf_name	the name of a UDF that returns a distinct or structured UDT.
argument	an argument to the UDF.
CAST	a CAST expression that converts a source data type to a distinct or structured UDT. Data type conversions involving UDTs require appropriate cast definitions for the UDTs. To define a cast for a UDT, use the CREATE CAST statement. For more information on CREATE CAST, see SQL Data Definition Language.
expression	an expression that results in a data type that is compatible as the source type of a cast definition for the target UDT.
udt_name	the name of a distinct or structured UDT data type.
NEW	an expression that constructs a new instance of a structured type and initializes it using the specified constructor method. For details on NEW, see “NEW” on page 1346.
SYSUDTLIB.	the database in which the constructor exists. Teradata Database only searches the SYSUDTLIB database for UDT constructors, regardless of whether the database name appears in the expression.
constructor_name	the name of a constructor method associated with a UDT. Constructor methods have the same name as the UDT with which they are associated.
argument	an argument to pass to the constructor. Parentheses must appear even though the argument list may be empty.
method_name	the name of an instance method that returns a UDT. For details on method invocation, see “Method Invocation” on page 1354.
argument	an argument to pass to the method. Parentheses must appear even though the argument list may be empty.

ANSI Compliance

UDT expressions are partially ANSI SQL:2011 compliant.

The requirement that parentheses appear when the argument list is empty is a Teradata extension to preserve compatibility with existing applications.

Authorization

To use a UDT expression, you must have the UDTTYPE, UDTMETHOD, or UDTUSAGE on the SYSUDTLIB database or the UDTUSAGE privilege on all of the specified UDTs.

Usage Notes  

You can use UDT expressions as input arguments to UDFs written in C or C++. You cannot use UDT expressions as input arguments to UDFs written in Java.

You can also use UDT expressions as IN and INOUT parameters of stored procedures and external stored procedures written in C or C++. However, you cannot use UDT expressions as IN and INOUT parameters of external stored procedures written in Java.

You can use UDT expressions with most SQL functions and operators, with the exception of ordered analytical functions, provided that a cast definition exists that casts the UDT to a predefined type that is accepted by the function or operator. For details, see other chapters in this book.

Examples  

Consider the following statements that create a distinct UDT named euro and a structured UDT named address:

   CREATE TYPE euro

   AS DECIMAL(8,2)

   FINAL;

   CREATE TYPE address

   AS (street VARCHAR(20)

      ,zip CHAR(5))

   NOT FINAL;

The following statement creates a table that defines an address column named location:

   CREATE TABLE european_sales

     (region INTEGER

     ,location address

     ,sales DECIMAL(8,2));

Example : Column Name

The following statement creates a table that defines an address column named location:

   CREATE TABLE italian_sales

     (location address

     ,sales DECIMAL(8,2));

The location column reference in the following statement returns an address UDT expression.

   INSERT INTO italian_sales

      SELECT location, sales

      FROM european_sales

      WHERE region = 1151;

Example : CAST

The following statement creates a table that defines a euro column named sales:

   CREATE TABLE swiss_sales

     (location address

     ,sales euro);

The following statement uses CAST to return a euro UDT expression. Using CAST requires a cast definition that converts the DECIMAL(8,2) predefined type to a euro type.

   INSERT INTO swiss_sales

      SELECT location, CAST (sales AS euro)

      FROM european_sales

      WHERE region = 1038;

Example : NEW

The following INSERT statement uses NEW to return an address UDT expression and insert it into the european_sales table.

   INSERT european_sales (1001, NEW address(), 0);

Example : Methods and Functions

The following statement uses the built-in constructor function and mutator methods to return a new instance of the address UDT and insert it into the european_sales table:

   INSERT INTO european_sales 

   VALUES (101, address().street('210 Stanton').zip('76543'), 500);

Teradata Database executes the UDT expression in the following order:

Step	Invocation	Result
1	address() constructor function	Default UDT instance
2	mutator method for street	UDT instance with street attribute set to '210 Stanton'
3	mutator method for zip	UDT instance with zip attribute set to '76543'

The final result of the UDT expression is an instance of the address UDT with the street attribute set to '210 Stanton' and the zip attribute set to '76543'.

Related Topics

FOR more information on …	SEE …
creating a UDT	CREATE TYPE in SQL Data Definition Language.
creating cast definitions for a UDT	CREATE CAST in SQL Data Definition Language.
using UDT expressions in DML statements such as SELECT and INSERT	CREATE TYPE in SQL Data Manipulation Language.