Specifying UDTs in an SQL Request - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantage™
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.