USING Variables - Teradata Database

The maximum number of USING variables you can specify in a USING request modifier is 2,550.

Each USING variable name must be unique in a USING request modifier.

The USING variable construct (:using_variable_name) is valid anywhere a character, numeric, or byte constant is valid. The constant values in the client system data record must match, item for item, the USING description of those values. Also, the constants in the data record must appear in the order in which they are defined in the USING clause.

You cannot use a USING variable to specify a column position in a GROUP BY or ORDER BY clause.

One USING clause can only modify one request. If several requests require USING variables, each request must be associated with its own USING row identifier.

You can pass USING variables in the arguments of a scalar UDF during invocation (see “Example 14: USING and SQL UDF Invocation” on page 530) when the data types of the USING variables match those of the UDF parameters, whether natively or after being explicitly cast. If the data types do not match, Teradata Database aborts the request and returns an error.

You can avoid this problem by casting the data types of the USING variables to the data types of the UDF parameters.

A USING modifier can be associated with one of the following types of requests:

Any single‑statement request except a DDL or DCL statement. An iterated request is a special case of this query type and must always have an associated USING modifier.

A multistatement request.

An explicit transaction. If the modifier is associated with an explicit transaction, the USING keyword must appear as the first keyword in that transaction.

If the first statement or request references a USING variable, the USING clause should immediately precede the BEGIN TRANSACTION statement (see “Example 3: USING Request Modifier Reads Character Strings” on page 527).

If you specify both a USING request modifier and an EXPLAIN request modifier (see “EXPLAIN Request Modifier” on page 552) for the same request, the EXPLAIN request modifier must precede the USING request modifier.

USING does not support UDTs explicitly. Client applications must transfer UDT data to the Teradata 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 Teradata platform value UDT by means of 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 SQL Data Definition Language 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 Teradata 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 about how to use these statements, see “CREATE CAST” and “CREATE TRANSFORM” in SQL Data Definition Language.

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 ;

The USING request modifier supports passing LOBs to the Teradata platform. The documentation for each Teradata Database‑supported API specifies how this functionality is presented to the application.

You can specify three different modes for handling large objects by the USING request modifier:

Inline

Deferred

Locator

Deferred and locator modes defer the transfer of data between client and server, transmitting non‑LOB data separately from LOB data. The appropriate deferred mode depends on the application. Neither deferred or locator mode passes the entire LOB field with the non‑LOB data from a row.