IF-THEN-ELSEIF-ELSE-END Behavior - Advanced SQL Engine - Teradata Database

1. The statements between the IF and ELSEIF boundaries execute when IF evaluates to TRUE. Control then passes to the statement following END IF.
2. The statements associated with each ELSEIF are evaluated for their truth value.
3. When a statement associated with an ELSEIF evaluates to TRUE, then the statements within its block execute. Subsequent ELSEIF clauses do not execute even if they evaluate to TRUE.
4. When no statement in any of the IF/ELSEIF blocks evaluates to TRUE, then the statement associated with the ELSE clause executes.

In the following example, one and only one of the DML statements associated with an ELSEIF or ELSE clause executes, depending on the value for hNoAccts.

IF hNoAccts = 1 THEN
  INSERT INTO temp_table VALUES (hNoAccts, 'One customer');
ELSEIF hNoAccts = 0 THEN
  INSERT INTO temp_table VALUES (hNoAccts, 'No customer');
ELSE
  INSERT INTO temp_table VALUES (hNoAccts, 'More than one customer');
END IF;

In the following example, one and only one of the DML statements associated with an ELSEIF or ELSE clause executes, depending on the value for hNoAccts.

IF hNoAccts = 1 THEN
  INSERT INTO temp_table VALUES (hNoAccts, 'One customer');
ELSEIF hNoAccts = 0 THEN
  INSERT INTO temp_table VALUES (hNoAccts, 'No customer');
ELSEIF hNoAccts < 0 THEN
  INSERT INTO temp_table VALUES (hNoAccts, 'Nonvalid customer');
ELSE
  INSERT INTO temp_table VALUES (hNoAccts, 'More than one customer');
END IF;