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.
- 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.
- 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.
- 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 ;
- The following request creates the transform group: