17.05 - ROLLBACK with a WHERE Clause - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

ROLLBACK tests each value separately, so a WHERE clause should not introduce both an aggregate and a nonaggregate value.

The aggregate value becomes, in effect, a GROUP BY value, and the mathematical computation is performed on the group aggregate results.

For example, assuming the following, the ROLLBACK statement that follows incorrectly terminates the transaction:
  • The table Test contains several rows,
  • The sum of Test.colA is 188, and
  • Only one row contains the value 125 in Test.colB
     ROLLBACK
     WHERE (SUM(Test.colA) <> 188)
     AND (Test.ColB = 125);

The preceding request is processed first by performing an all-rows scan with the condition (ColB  = 125), which selects a single row and then computes intermediate aggregate results with the condition (SUM(ColA) <> 188).

The condition evaluates to TRUE because the value of ColA in the selected row is less than 188.

If ROLLBACK … WHERE is used and the statement requires READ access to an object for execution, then user executing the ROLLBACK statement must have SELECT right to the data being accessed.

The WHERE condition of a ROLLBACK can include subqueries. The subqueries require FROM clauses and the ROLLBACK request should also have a FROM clause if you want the scope of a reference in a subquery to be the ROLLBACK condition.