Specifying UDTs in an SQL Request
The rules and restrictions are:
A UDT must have its tosql and fromsql transform functionality defined prior to its
use as a column data type for any table. See “CREATE TRANSFORM” in SQL Data Definition Language.
You can specify an expression that returns a UDT in a column list only if its transform
group has a fromsql routine. The system automatically converts the expression from
its UDT value to the external type via the fromsql routine before returning it to
a client application.
The only difference between distinct and structured types in this regard is that Teradata
Database generates a transform group with both fromsql and tosql routines by default
for distinct types, and its external data type is the source data type, while you
must explicitly create the transform group for a structured type. For details on how
to create a transform group for a UDT, see “CREATE TRANSFORM” in SQL Data Definition Language.
If you submit a SELECT statement with no table references that contains a UDT expression
in its select list, and you have not declared transform functionality for it using
a UDT literal declared using a NEW expression, then the request aborts and the system
returns an error. For information about the NEW expression, see SQL Functions, Operators, Expressions, and Predicates.