17.05 - Specifying UDTs in an SQL Request - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)
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.