17.05 - USING Support for UDTs - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

USING does not support UDTs explicitly. Client applications must transfer UDT data to the Vantage platform in its external type form, which is always a predefined data type. You can then use either an implicit or explicit cast to import UDTs by means of the USING request modifier.

Implicit casts are invoked during assignment operations, including: INSERT, UPDATE, and parameter passing operations.

When both of the following conditions are true, the system automatically converts the client value predefined data type to the appropriate Vantage platform value UDT using an implicit casting operation:
  • A host variable is involved in an assignment operation and is assigned to a UDT target
  • An implicit cast is defined. The implicit cast can be either of the following:
    • A system-generated implicit cast.
    • An explicitly created implicit cast.

      See CREATE CAST in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144 for details.

If no applicable implicit cast has been defined, you must perform explicit conversions using constructor methods, casts, UDFs, and so on, as indicated by the following examples:
  • The following example shows an implicit cast conversion from a client predefined data type to a Vantage platform distinct UDT:
         USING (europe_sales DECIMAL(9,2))
         INSERT INTO tab1 VALUES (:europe_sales, 1);
  • The following example shows an explicit conversion and construction operation using a constructor method:
         USING (street VARCHAR(20), zip CHARACTER(5))
         INSERT INTO tab2 VALUES (NEW address(:street, :zip), 2 );
  • The following example shows conversion by means of method invocation:
         USING (price DECIMAL(6,2))
         UPDATE tab1
         SET column1 = CAST(:price AS euro).roundup(0);

Best practices for UDT design recommend that you duplicate all transform group functionality to enable optimum support for the USING request modifier. You can do this by coding equivalent predefined external data type-to-UDT and UDT-to-predefined external data type casts. The simplest way to accomplish this is to reference the same routines in the equivalent CREATE CAST and CREATE TRANSFORM statements. For details, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.

If you follow these guidelines, then iterated array processing is also supported. UDTs cannot be referenced for array processing directly in a USING request modifier, but can be populated by using the implicit cast (from predefined data type-to-UDT) mechanism.

Distinct and structured UDTs differ in these requirements:
  • For a distinct UDT, if you plan to use only its system-generated functionality, no work is required because the transform and implicit casting functionality has already been defined.
  • For a structured UDT, you must define the transform and implicit casting functionality explicitly with CREATE CAST and CREATE TRANSFORM statements, as indicated by the following examples for a UDT named address:
    • The following request creates the transform group:
           CREATE TRANSFORM FOR address client_io (
            TO SQL WITH SPECIFIC FUNCTION SYSUDTLIB.stringToAddress,
            FROM SQL WITH SPECIFIC METHOD toString);
    • The following request creates an implicit cast from VARCHAR(100) to address that duplicates the tosql functionality of the client_io transform group:
           CREATE CAST ( VARCHAR(100) AS address )
            WITH SPECIFIC FUNCTION SYSUDTLIB.stringToAddress
            AS ASSIGNMENT;
    • The following request creates an implicit cast from address to VARCHAR(100) that duplicates the fromsql functionality of the client_io transform group:
           CREATE CAST (address AS VARCHAR(100))
            WITH SPECIFIC METHOD ToString AS ASSIGNMENT ;