15.00 - UDT-to-UDT Conversion - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

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

UDT-to-UDT Conversion

Purpose

Converts a UDT expression to a different UDT type.

CAST Syntax

where:

 

Syntax element …

Specifies …

UDT_expression

an expression that results in a UDT data type.

For details on expressions that can result in UDT data types, see “SQL UDF” on page 1318.

UDT_data_definition

a UDT type to which UDT_expression is to be converted, followed by any of the following optional attribute phrases:

  • FORMAT
  • NAMED
  • TITLE
  • ANSI Compliance

    This is ANSI SQL:2011 compliant.

    As an extension to ANSI, CAST permits the use of data attribute phrases such as FORMAT.

    Usage Notes

    Explicit UDT-to-UDT conversion using Teradata conversion syntax is not supported.

    Data type conversions involving UDTs require appropriate cast definitions for the UDTs. To define a cast for a UDT, use the CREATE CAST statement. For more information on CREATE CAST, see SQL Data Definition Language.

    Implicit Type Conversion

    Teradata Database performs implicit UDT-to-UDT casts for the following operations:

  • UPDATE
  • INSERT
  • Passing arguments to stored procedures, external stored procedures, UDFs, and UDMs
  • Specific system operators and functions identified in other sections of this book, unless the DisableUDTImplCastForSysFuncOp field of the DBS Control Record is set to TRUE
  • An implicit data type conversion involving a UDT can only be performed if the cast definition specifies the AS ASSIGNMENT clause. For more information, see “CREATE CAST” in SQL Data Definition Language.

    Example  

    Consider the following table definitions, where euro and us_dollar are UDTs:

       CREATE TABLE euro_sales_table
          (euro_quarter INTEGER
          ,euro_region VARCHAR(20)
          ,euro_sales euro );
     
       CREATE TABLE us_sales_table
          (us_quarter INTEGER
          ,us_region VARCHAR(20)
          ,us_sales us_dollar );

    Assuming an appropriate cast definition exists for converting the euro UDT to a us_dollar UDT, the following statement performs a us_dollar UDT to euro UDT conversion:

       INSERT INTO euro_sales_table
          SELECT us_quarter, us_region, CAST (us_sales AS euro) 
          FROM us_sales_table;

    Related Topics

    For details on data types and data attributes, see SQL Data Types and Literals.