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.
- 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.