USING Support for UDTs - Teradata Vantage

Teradata® VantageCloud Lake

Deployment

VantageCloud

Edition

Lake

Product

Teradata Vantage

Published

January 2023

Language

English (United States)

Last Update

2024-04-03

dita:mapPath

phg1621910019905.ditamap

dita:ditavalPath

pny1626732985837.ditaval

dita:id

phg1621910019905

Structured/Distinct/Variant UDTs are only supported on the Block File System on the primary cluster. They are not available for the Object File System.

USING does not support UDTs explicitly. Client applications must transfer UDT data to the Vantage platform in its external type form, which is 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.

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.

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 ;
```