15.10 - CASE DML Expressions - Parallel Transporter

Teradata Parallel Transporter User Guide

Parallel Transporter
User Guide

CASE DML Expressions

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> 

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'',
     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;'