Coding the APPLY Statement - Parallel Transporter

Coding the APPLY Statement

An APPLY statement typically contains two parts, which must appear in the order shown:

1 A DML statement (such as INSERT, UPDATE, or DELETE) that is applied TO the consumer operator that will write the data to the target, as shown in Figure 16. The statement may also include a conditional CASE or WHERE clause.

Figure 16: Multiple Insert Statements

2 For most jobs, the APPLY statement also includes the read activity, which uses a SELECT FROM statement to reference the producer operator. If the APPLY statement uses a standalone operator, it does not need the SELECT FROM statement.

Note: In Figure 17, the SELECT statement also contains the UNION ALL statement to combine the rows from two SELECT operations against separate sources, each with its own operator.

Figure 17: SELECT Statement in an APPLY Statement

Derived Column Data Types

Derived columns, which have values derived from the evaluation of expressions, require derived column names. A derived column name must be defined in the schema of a job script, and if multiple schemas are used, identically defined in all schemas.

The following warnings and errors can occur:

Incompatibility between the schema-defined derived column and the resulting data type attributes of the expression, such as assigning a numeric value to a CHAR column.

An error results when the script is compiled, and the job terminates.

An incompatibility such as the value of a numeric expression being outside the range of the data type of its derived numeric column, which can be detected only during execution.

An error results, and the job terminates.

Truncated characters due to an incompatibility in character data type length.

When the script is compiled, one warning is generated for every applicable derived column, but no run-time message is generated if truncation occurs.

Using the DDL Operator in an APPLY Statement

The DDL operator can be specified in the APPLY statement in either single or multi-statement format. To execute each statement as its own transaction, you should have one SQL statement per DML group (enclosed in parentheses).

If more than one statement is specified in a DML group, the operator combines them all into a single multi-statement request and sends it to the Teradata Database as one transaction. Teradata Database enforces the rule that a multi-statement DML group can have only one DDL statement and it must be the last statement in the transaction, which means the last statement in the group. The SQL statements are executed by groups in the order they are specified in the APPLY statement. If any statement in the group fails, then all statements in that group are rolled back and no more groups are processed.

The following is a simplified example of a DDL operator in a single-statement format:

The following is a simplified example of a DDL operator in a multi-statement format:

Using the Update Operator to Delete Data

Use the Update operator with the DeleteTask attribute to delete data from the Teradata Database. The Update operator functions as either a standalone or a consumer operator, depending on whether or not data is required to complete the deletion.

Consider the following rules when using the DeleteTask feature:

The Delete Task feature may not be used on a database view.

Only one special session will be connected.

Only one instance may be specified.

Only one DML group may be specified.

Only one DML DELETE statement in the DML group may be specified.

Only one target table may be specified.

The first of the error tables (the acquisition error table) is not used and is ignored.

Only one data record may be provided if using a WHERE clause. For example, you can send more than one row to the data stream (from the producer operator), but only the first one is used.

For further information on use of the DELETE task with a standalone Update operator, see Teradata Parallel Transporter Reference.