15.00 - System-Generated Default Functionalities For Distinct UDTs - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1184-015K
Language
English (United States)

System‑Generated Default Functionalities For Distinct UDTs

Once you have created a distinct UDT, Teradata Database automatically generates the following additional UDT‑related functionality for you.

  • A transform group with one transform that maps the distinct UDT to its source data type for use by client applications, and another that maps the source data type to its corresponding distinct UDT for Teradata Database applications.
  • 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” on page 812 and “CREATE TRANSFORM/ REPLACE TRANSFORM” on page 694).

    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.

    See “CREATE TRANSFORM/ REPLACE TRANSFORM” on page 694 for more information about transforms.

  • A full map ordering to support comparisons if the source type is not a LOB.
  • 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.

    When the system compares two distinct UDT values, it pursues the following process:

    a Convert the UDTs to their source data types.

    b Compare the source data types.

    The result of this comparison is used as the result of the distinct UDT comparison.

    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/ REPLACE ORDERING” on page 416 for more information about orderings.

  • A cast for converting a distinct UDT value to a source type value.
  • 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).

    An assignment operation is defined as any of the following SQL operations:

  • Inserts
  • Updates
  • Parameter passing
  • You can optionally define additional casting operations and drop or replace the system‑generated casts

    See “CREATE CAST/ REPLACE CAST” on page 212 for more information about casts.

  • A cast for converting a source type value to a distinct UDT value.
  • 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).

    An assignment operation is defined as any of the following SQL operations:

  • Inserts
  • Updates
  • Parameter passing
  • You can optionally define additional casting operations and drop or replace the system‑generated casts

    See “CREATE CAST/ REPLACE CAST” on page 212 for more information about casts.

    Note that you can drop transforms, orderings, and casts only if the associated UDT is not being used as the column data type in any table. If you attempt to drop the ordering or transform functionality for a UDT being used as a table column type, the system returns an error to the requestor.

    You can replace the transforms, orderings, and casts associated with a UDT without having to first drop any columns that have the type.