System-Generated Default Functionalities For Distinct UDTs - Advanced SQL Engine - Teradata Database

The system assigns a name to the generated transform group in the format TD_identifier, truncated to 30 characters, where identifier is the unqualified part of the distinct UDT name.

This transform group contains both tosql and fromsql functionality, supporting the import and export of the UDT between the client system and the Teradata Database.

You can drop or replace this system-generated transform functionality using the DROP TRANSFORM and REPLACE TRANSFORM statements, respectively (see DROP TRANSFORM and CREATE TRANSFORM and REPLACE TRANSFORM).

The system-generated fromsql functionality converts a distinct data type value to its corresponding source data type value.

Conversely, the system-generated tosql functionality converts a source data type value to a distinct data type value. The external type of a distinct data type is, by default, its source data type.

If a distinct UDT has a defined transform group, whether system-generated or user-defined, and the column list of a SELECT statement contains a distinct UDT column or expression, the system automatically converts the distinct type to its external type by means of its defined fromsql transform routine.

Unless the source type is a LOB, the system automatically create an ordering such that when two values of a distinct type are compared, the corresponding source type values are compared. For example, when two euro values are compared, the result is the same as comparing two DECIMAL values.

You cannot compare a distinct UDT column or expression directly with a source type value. Either the distinct UDT value or the source type value must first be converted to the other by means of explicit built-in casts.

Similarly, you cannot compare different distinct types directly; you must first convert one of the distinct UDTs to the other type by means of a user-defined cast.

As in the case of the system-generated transform functionality, you can drop or replace the ordering functionality the system generated by default and replace it with your own transforms.

When the source type is a LOB, special rules apply. Suppose that the source type of a distinct type named image is BLOB. By default, no comparison operation is supported because you cannot compare LOB data types. Note, however, that although no system-generated ordering functionality is produced, you must still define an ordering for image before you can use it to define the column type of any table. An attempt to use a newly created data type that has no ordering defined as the type of any column of any table returns an error to the requestor.

Because UDTs follow the rules of strong typing, you cannot directly compare a distinct type value with a value that has its source data type. Instead, you must explicitly cast the source type value to the distinct UDT using the system-generated cast, or vice versa, before you perform a comparison between them.

See CREATE ORDERING and REPLACE ORDERING for more information about orderings.

You can invoke this cast either implicitly or explicitly during assignment operations. The syntax for explicitly invoking this cast is as follows:

    CAST (distinct_UDT_expression  AS  predefined_data_type).

You can invoke this cast either implicitly or explicitly during assignment operations. The syntax for explicitly invoking this cast is as follows:

    CAST (predefined_type_expression  AS  distinct_UDT_name).