15.00 - UDT Expression - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)

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.