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 Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
- 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 the 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 Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
- 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 Teradata Vantage™ - SQL Operators and User-Defined Functions, B035-1210.