IF-THEN-ELSEIF-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.
4. When no statement in the IF/END IF block evaluates to TRUE, then none of the statements can execute.

In the following example, either one and only one of the ELSEIF clauses executes its associated DML statement or none does, 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');
END IF;

In the following example, one and only one of the ELSEIF clauses executes its associated DML statement or none does, 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, 'Unknown customer');
END IF;