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

Purpose

Provides conditional execution of statements based on the evaluation of the specified conditional expression or equality of two operands.

The CASE statement is different from the SQL CASE expression, which returns the result of an expression.

Invocation

Executable

Stored procedures only.

Syntax 1

CASE operand_1
  when_clause [...]
  [ ELSE statement [...] ]
END CASE ;

when_clause

WHEN operand_2 THEN statement [...]

Syntax 2

CASE
  when_clause [...]
  [ ELSE statement [...] ]
END CASE ;

when_clause

WHEN conditional_expression THEN statement [...]

statement

{ SQL_statement |
  compound_statement |
  assignment_statement |
  condition_statement |
  [ label_name : ] iteration_statement [ label_name ] |
  ITERATE label_name |
  LEAVE label_name
}

compound_statement

[ label_name : ] BEGIN
  [ local_declaration ] [...]
  [ cursor_declaration ] [...]
  [ condition_handler ] [...]
  [ statement; ] [...]
END [ label_name ] ;

assignment_statement

SET assignment_target = assignment_source

condition_statement

{ CASE_statement | IF_statement }

iteration_statement

{ WHILE conditional_expression
    DO statement; [...] |

  LOOP
    statement; [...]
  END LOOP |

  FOR for_loop_variable AS [ cursor_name CURSOR FOR ] cursor_specification
    DO statement; [...]
  END FOR |

  REPEAT
    statement; [...]
    UNTIL conditional_expression
  END REPEAT
}

local_declaration

DECLARE {
  variable_name [,...] data_type [ DEFAULT { literal | NULL } ] |
  condition_name CONDITION [ FOR SQLSTATE [VALUE] sqlstate_cod ]
} ;

cursor_declaration

DECLARE cursor_name [ [NO] SCROLL ] CURSOR
  [ WITHOUT RETURN |
    WITH RETURN [ONLY] [ TO { CALLER | CLIENT } ]
  ]
  [ FOR { cursor_specification [ FOR { READ ONLY | UPDATE } ] |
          statement_name
        } 
  ] ;

IF_statement

See IF.

CASE_statement

Either form of the CASE statement.

condition_handler

DECLARE { CONTINUE | EXIT } HANDLER FOR
  { 
    { SQLSTATE [ VALUE ] sqlstate_code | condition_name } [,...] |

    { SQLEXCEPTION | SQLWARNING | NOT FOUND } [,...]

  } handler_action_statement ;

cursor_specification

SELECT cursor_spec [,...]
  FROM {
    table_name [,...] |

    table_name {
      INNER |
      { LEFT | RIGHT | FULL } OUTER
    } JOIN table_name ON condition
  }

cursor_spec

{ column_name [ [AS] alias_name ] |

  expression [AS] alias_name |

  *
}

ANSI Compliance

CASE is ANSI/ISO SQL:2011-compliant.

Authorization

None.

Semantic Differences Between CASE Statement and CASE Expression

The semantics for stored procedure CASE statements and the CASE expression of ordinary interactive SQL are different. For example, expressions have values, but statements do not; expressions cannot be executed, but statements can; expressions can be combined with other expressions, but statements cannot. See “CASE Expressions” in Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145.

CASE Statement Forms

The alternative to using CASE statements is using an IF-THEN-ELSEIF-ELSE statement.

CASE statements are generally preferred when there are more than two conditions or values to be checked.

Simple CASE Statement

In this form of the conditional statement, you can execute a list of SQL statements, including control statements, associated with at most one WHEN clause or ELSE clause, depending on whether operand_1 (value-expression) equals operand_2 (value-expression).

The WHEN clauses are evaluated in the order in which they are specified in the CASE statement. The process of evaluation is as follows:

1. The first WHEN clause is evaluated.
   • If the value-expression (operand_1)specified in the CASE clause is equal to the value-expression (operand_2)in the WHEN clause, the statements of that WHEN clause are executed.
   • Control goes to the next statement in the stored procedure.If the value expressions are not equal, then the next WHEN clause, if it exists, is evaluated.
2. All subsequent WHEN clauses are evaluated as described in stage 1.
3. When there are no more WHEN clauses to evaluate, the ELSE clause, if it exists, is taken up and the statements of the ELSE clause are executed. Control goes to the next statement in the stored procedure.
4. If there is no ELSE clause and the value-expression in the CASE clause does not find a match in any of the WHEN clauses,

• A runtime exception (“Case not found for CASE statement”, SQLSTATE=’20000’, SQLCODE = 7601) occurs.
• The execution of the CASE statement is terminated.

Searched CASE Statement

This form of the CASE statement executes a list of statements when the conditional expression in the WHEN clause evaluates to true. You can execute the statements associated with at most one WHEN clause or ELSE clause.

The WHEN clauses are evaluated in the order in which they are specified in the CASE statement. The process of evaluation is as follows:

1. The first WHEN clause is evaluated.
   • If the conditional expression specified in the WHEN clause is true, the statements of that WHEN clause are executed.
   • Control moves to the next statement in the stored procedure.

   If the conditional expression is not true, then the next WHEN clause, if it exists, is evaluated.

2. All subsequent WHEN clauses are evaluated as described in stage 1.
3. When there are no more WHEN clauses to evaluate, the ELSE clause, if exists, is taken up and the statements of the ELSE clause are executed.

   Control moves to the next statement in the stored procedure.

4. If there is no ELSE clause and the conditional expression in none of the WHEN clauses evaluates to true,
   • a runtime exception (“Case not found for CASE statement”, SQLSTATE=’20000’, SQLCODE = 7601) occurs.
   • the execution of the CASE statement is terminated.

Exception Handling in CASE Statements

If a statement following a WHEN or ELSE clause raises an exception and the stored procedure contains a handler to handle the exception condition, the behavior is identical to exceptions occurring within an IF or WHILE statement.

If the value expression or conditional expression of a CASE statement raises an exception and the stored procedure contains a CONTINUE handler to handle the exception condition, the control moves to the statement following END CASE, after the condition handler action completes successfully.

Example: Simple CASE

The following stored procedure includes a simple CASE statement.

CREATE PROCEDURE spSample(IN  pANo    INTEGER,
                          IN  pName   CHARACTER(30),
                          OUT pStatus CHARACTER(50))
BEGIN
  DECLARE vNoOfAccts INTEGER DEFAULT 0;
   SELECT COUNT(*) INTO vNoOfAccts FROM Accounts;
     CASE vNoOfAccts
        WHEN 0 THEN
            INSERT INTO Accounts (pANo, pName);
        WHEN 1 THEN
            UPDATE Accounts
            SET aName = pName WHERE aNo = pANo;
        ELSE
            SET pStatus = 'Total ' || vNoOfAccts || 'customer                                            accounts';
    END CASE;
END;

In the preceding example, the appropriate SET statement of a WHEN clause is executed depending on the value of the local vNoAccts.

INSERT INTO Accounts (pANo,               pName);

UPDATE Accounts 
    SET aName = pName 
   WHERE aNo = pANo;

SET pStatus = 'Total ' || vNoAccts    || ' customer accounts';

Example: Searched CASE

The following stored procedure includes a searched CASE statement.

CREATE PROCEDURE spSample (IN pANo INTEGER,
                   IN pName CHARACTER(30), OUT pStatus CHARACTER(50))
BEGIN
  DECLARE vNoAccts INTEGER DEFAULT 0;
   SELECT COUNT(*) INTO vNoAccts FROM Accounts;
   CASE
      WHEN vNoAccts = 0 THEN
          INSERT INTO Accounts (pANo, pName);
      WHEN vNoAccts = 1 THEN
          UPDATE Accounts
            SET aName = pName WHERE aNo = pANo;
      WHEN vNoAccts > 1 THEN
            SET pStatus = 'Total ' || vNoAccts || ' customer                                            accounts';
   END CASE;
END;

In the preceding example, the appropriate SET statement of a WHEN clause is executed depending on the value of the local variable vNoAccts.

INSERT INTO Accounts (pANo, pName);

UPDATE Accounts 
   SET aName = pName 
    WHERE aNo = pANo;

SET pStatus = ’Total’ ||
vNoAccts || ’customer accounts’;

If the value of vNoAccts is NULL, the stored procedure raises a runtime exception (“Case not found for CASE statement”, SQLSTATE=’20000’, SQLCODE = 7601) in the absence of the ELSE clause. However, vNoAccts cannot be set to NULL by this example.

Example: Using FOR Loop Aliases in Searched CASE

The following example illustrates the use of FOR loop aliases in the conditional expressions of a searched CASE statement:

CREATE PROCEDURE spSample()
Label1:BEGIN
    FOR RowPointer AS
        c_employee CURSOR FOR
        SELECT DeptNo AS c_DeptNo,
            employeeid AS c_empid FROM Employee
    DO
        CASE
     WHEN RowPointer.c_DeptNo > 10 THEN
        INSERT INTO Dept VALUES (RowPointer.c_DeptNo,
                                RowPointer.c_empid) ;
     WHEN RowPointer.c_DeptNo <= 10 THEN
        UPDATE Employee
           SET DeptNo = RowPointer.c_DeptNo + 10 ;
        INSERT INTO Dept VALUES (RowPointer.c_DeptNo,
                                RowPointer.c_empid)
   END CASE;
    END FOR;
END Label1;

Related Topics