ROLLBACK with a WHERE Clause - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantage™

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 all the following, the ROLLBACK statement that follows incorrectly terminates the transaction:
  • The table Test contains several rows.
  • The sum of Test.colA is 188.
  • 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.