IF | Teradata Vantage - IF - Advanced SQL Engine - Teradata Database

SQL Stored Procedures and Embedded SQL

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
xqq1557098602407.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1148
lifecycle
previous
Product Category
Teradata Vantage™

Purpose

Provides conditional execution based on the truth value of a condition.

Invocation

Executable

Stored procedures only.

Syntax

IF conditional_expression THEN statement [...]
  [ ELSEIF conditional_expression THEN statement [...] ]
  [ ELSE statement [...] ]
END IF ;
conditional_expression
A boolean condition used to evaluate whether a statement or statements embedded within the IF block should be executed.
You cannot use IN and NOT IN operators if the conditional list contains any local variables, parameters, or cursor aliases.
OUT parameters are not allowed in conditional_expression.
statement
Any of the following:
  • DML, DDL or DCL statement that can be used in a stored procedure. These include dynamic SQL statements.
  • Control statements, including BEGIN … END compound statements.
For details, see statement in FOR.

ANSI Compliance

IF is ANSI/ISO SQL:2011-compliant.

Authorization

None.

ELSEIF Rule

You can specify an unlimited number of ELSEIF clauses in an IF statement, but each must be associated with a condition as in the case of the initial IF clause.

Valid Forms of the IF Statement

  • IF-THEN-END IF
  • IF-THEN-ELSE-END
  • IF-THEN-ELSEIF-END
  • IF-THEN-ELSEIF-THEN-ELSE-END

IF-THEN-END IF

This form of IF executes the statements within the IF and END IF bounds when conditional_expression evaluates to TRUE.

The following statement is an example of IF-THEN-END IF:

   IF hNoAccts = 1 THEN
     INSERT INTO temp_table VALUES (hNoAccts, 'One Customer');
   END IF;

IF-THEN-ELSE-END IF

This form of IF executes the statements within the IF and ELSE bounds when conditional_expression evaluates to TRUE. Otherwise, the statements within the ELSE and END IF bounds execute.

In the following example, only one of the specified INSERT statements executes, depending on the value for hNoAccts.

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

IF-THEN-ELSEIF-END Behavior

  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;

IF-THEN-ELSEIF-ELSE-END Behavior

  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;