Function of Casting UDTs | CREATE/REPLACE CAST | Teradata Vantage - Function of Casting UDTs - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Advanced SQL Engine
Teradata Database
Release Number
July 2021
English (United States)
Last Update
Product Category
Teradata Vantage™

A cast operation converts the specified source_data_type to the specified target_data_type using the specified cast_function or cast_method routine analogous to the CAST operator used to convert predefined data types. For more information about the CAST operator, see Teradata Vantage™ - Data Types and Literals, B035-1143. At least one of source_data_type or target_data_type must be a UDT (see CREATE TYPE (Distinct Form) and CREATE TYPE (Structured Form)).

Casts are the mechanism the system uses to make conversions in either direction between a UDT and another data type, which might or might not be another UDT. UDTs can have multiple casts, where each cast supports the conversion for a particular source-target combination.

For example, the UDT named circle_type might have the following four separate casts:
  • circle_type to VARCHAR
  • VARCHAR to circle_type
  • circle_type to VARBYTE
  • VARBYTE to circle_type

After you create a user-defined cast, it is immediately available for use with the SQL CAST function. See Teradata Vantage™ - Data Types and Literals, B035-1143. For example, if you define a cast to convert a circle UDT to a VARCHAR(40), you can specify the following cast operation:

CAST(circle_udt_expression AS VARCHAR(40))

The system automatically and implicitly supports casts from or to a predefined data type when you cast a compatible predefined type to the appropriate predefined type serving as the cast source of target type.

The scope of operations under which the system applies UDT-related implicit casts is a subset of the scope of operations under which traditional predefined data type-related implicit casts are applied. See Teradata Vantage™ - Data Types and Literals, B035-1143.

For example, consider the following equality comparison in a WHERE clause:

WHERE myUdt = NEW CircleUdt(1,1,9)

This is not within the scope of the implicit casts the system supports because it is not an assignment operation, supported system operator, or supported predefined function, so no implicit casts are applied. You must specify explicit cast operations to ensure that the UDTs being compared are identical.