UDT-to-UDT Conversion - Teradata Database

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.