Multi-Statement Example | Basic Teradata Query - 17.00 - Multi-Statement Example - Basic Teradata Query

Basic Teradata® Query Reference

Basic Teradata Query
June 2020
Programming Reference

To submit the same three statements as a multi-statement request, use the following syntax:

SELECT * FROM Employee
; DELETE FROM Employee WHERE Name = 'Inglis C' AND Empno = 10014
; SELECT Name FROM Employee;

BTEQ sends only one request at a time to any one available database user session. If an error occurs that indicates the request failed but can be retried, BTEQ resubmits the request. BTEQ initiates no further action.

Because BTEQ passes SQL to the database without interpreting it, it is not transaction state aware. This means it cannot exercise control over a transaction's state. As such, the state must be determined solely by the method used to employ BEGIN TRANSACTION (BT), END TRANSACTION (ET), and ABORT for Teradata mode, or COMMIT and ROLLBACK for ANSI mode. Therefore, users must consider BTEQ's “unawareness” as well as the differences between ANSI and Teradata transaction semantics modes when it is important for a transaction associated with one or more submitted requests to be entirely retryable, or when a transaction must span requests.

In Teradata semantics mode, regardless of the method used to identify its transaction (implicit, as in a multi-statement request or macro execution, or explicit, using BT/ET), a request or statement's failure results in rollback of that transaction. It is the user's responsibility to decide what to do as a result of such rollbacks: redrive the rolled-back portion of the transaction (after correcting whatever condition led to the error), abandon the transaction (that is, bypass the remaining requests or statements that would have been submitted as part of the transaction), or take other action appropriate to the particular circumstances.

In ANSI semantics mode, a transaction is always active unless it has just completed or has been reversed by a COMMIT or ROLLBACK, respectively. An ANSI mode statement failure results in a rollback of the request, not the transaction. It is the user's responsibility to correct and redrive the request, submit a ROLLBACK statement to rollback the remainder of the already-processed transaction, or take other action appropriate for the particular circumstances.

For a complete discussion of Teradata versus ANSI modes and their effect on transaction semantics, see Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142.