16.10 - Grouping SQL Statements in the Teradata PT APPLY Statement - Parallel Transporter

Teradata Parallel Transporter Reference

Product
Parallel Transporter
Release Number
16.10
Published
July 2017
Content Type
Programming Reference
Publication ID
B035-2436-077K
Language
English (United States)

You can group several SQL statements together to perform a desired logical database task and still take advantages of the automatic rollback feature on Teradata Database in case of any statement failures or any errors occurring during the transaction.

However, you should have one SQL statement per group if you desire to execute each statement in its own transaction.

When multiple SQL statements are specified in one DML group in the APPLY statement, Teradata Database enforces the rule that if the group contains a DDL statement, it must be the last statement in the implicit transaction, which means the last statement in a group.

Therefore, given that the information in parentheses (below) represents a group, the validity of the statements can be determined as follows:

  • Group 1: (DDL) is valid.
  • Group 2: (DDL, DDL) is invalid because only one DDL statement is allowed.
  • Group 3: (DML, DML, DDL) is valid.
  • Group 4: (DML, DML, DML) is valid, even though the group contains no DDL statement.
  • Group 5: (DML, DDL, DML) is invalid because the DDL statement is not the last statement in the group.

If a script contains unsupported or invalid statements, the job terminates so the script can be fixed before continuing.