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

SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
imq1591724555718.ditamap
dita:ditavalPath
imq1591724555718.ditaval
dita:id
B035-1184
lifecycle
previous
Product Category
Teradata Vantage™
Once you have created a distinct UDT, Vantage 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 Vantage 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 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.

  • 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:
    1. Convert the UDTs to their source data types.
    2. 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 and REPLACE ORDERING 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 and REPLACE CAST 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

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.