Request Types - Basic Teradata Query

Basic Teradata Query Reference

Product
Basic Teradata Query
Release Number
15.00
Language
English (United States)
Last Update
2018-09-25
dita:id
B035-2414
lifecycle
previous
Product Category
Teradata Tools and Utilities

Request Types

There are two types of Teradata SQL requests:

  • A single-statement request is a single Teradata SQL statement sent as a request.
  • A multi-statement request is two or more statements that are sent as a request.
  • Semicolon placement in relation to the rest of a line (for example, at the beginning or end) determines whether a statement is processed as a single-statement request or a multi-statement request.

     

    Request Type

    Semicolon Placement

    Single-statement request

    Place the semicolon at the end of the statement as the last nonblank character of the line.

    Multi-statement request

    Place the semicolon at the beginning of the line that begins the second through the final statement, and at the end of the final statement as the last nonblank character of the line.

    Single-Statement Example

    BTEQ submits the following statements to the Teradata Database as three single-statement requests:

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

    Multi-Statement Example

    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 Teradata Database 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 forTe rad at a 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 tot he particular circumstances.

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