CASE DML expressions allow Teradata PT jobs to require that each source row satisfy one of a number of conditions before being applied to any data targets, such that these conditions control which groups of DML statements are applied to the target table.
The following example shows typical CASE DML expressions structure:
CASE WHEN <condition 1> THEN <DML expression 1> WHEN <condition 2> THEN <DML expression 2> : : : : WHEN <condition n> THEN <DML expression n> ELSE <DML expression n+1> END
The conditions in a CASE DML expression are evaluated one by one from left to right; the first condition that is met for a given row causes the Teradata PT to apply the DML statement(s) in the corresponding DML expression to the row. The DML statements in the optional ELSE’s DML expression will be applied by default, if none of the conditions are met.
The conditions can be simple predicates that reference column values in the source row, or they can be arbitrarily complex predicates that consist of simple predicates joined by logical ANDs and ORs. Any value in an expression can be specified as a CASE value expression.
The following is a typical use for the CASE DML expression:
CASE DML Expression Example
CASE WHEN ( Expected_Arrival_Time = Scheduled_Arrival_Time ) THEN 'UPDATE Flight_Status_Board SET Flight_Status = ''On Time'', Gate_Number = :Scheduled_Gate_Number, Carousel_Number = :Scheduled_Carousel_Number;' WHEN ( Expected_Arrival_Time > Scheduled_Arrival_Time ) THEN ('UPDATE Flight_Status_Board SET Flight_Status = ''Delayed'', Arrival_Time = :Expected_Arrival_Time;', 'INSERT INTO LAX.AIRPORT_OPERATIONS(:Flight_Number, ''Seat of the Pants Airlines'', :Passenger_Count);') WHEN ( Expected_Arrival_Time = 0 ) THEN ('UPDATE Flight_Status_Board SET Flight_Status = ''Cancelled'', Gate_Number = NULL, Carousel_Number = NULL;', 'DELETE FROM Pending_Arrivals WHERE Flight_Number = :Flight_Number AND Airline = ''Seat of the Pants'';') ELSE 'UPDATE Flight_Status_Board SET Flight_Status = ''Early'', Arrival_Time = :Expected_Arrival_Time;' END