17.05 - Example: Non-Valid Use of Subquery in WHEN Clause - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Published
January 2021
Content Type
Programming Reference
Publication ID
B035-1144-175K
Language
English (United States)
Last Update
2021-01-22

The following WHEN clause is not valid because the subquery in the search condition returns multiple values and therefore cannot evaluate to either TRUE or FALSE:

     CREATE TRIGGER trigwhen4
       AFTER INSERT ON t1
       REFERENCING NEW AS NewRow
     FOR EACH ROW
       WHEN (t2.a > (
         SELECT b 
         FROM t2 
         WHERE t2.c < 5))
     ABORT;

If you insert multiple values into t1, the SELECT request returns an “unknown” response, which the WHEN condition cannot evaluate, and the transaction fails.

     *** Failure 3669 More than one value was returned by a subquery.