CASE function

CASE is a complex function that has two forms; the simple-when form and the searched-when form. In either form CASE returns a result, the value of which controls the path of subsequent processing.

Syntax

simple-when-clause

searched-when-clause

In the simple-when form, source_value is compared with each test_value until a match is found. The result of the CASE function is the value of the corresponding result_value. The data type of source_value must therefore be comparable to the data type of each test_value.

The CASE function must have at least one WHEN clause. The ELSE expression is optional. The default ELSE expression is NULL. A CASE expression is delimited by END. The test values do not have to be literal values.

The searched-when form is similar, but has the additional flexibility of allowing a number of different values to be tested.

The following example shows a CASE function with a simple WHEN clause. In this example, the CASE can be determined only by one variable that is specified next to the CASE keyword.

 DECLARE CurrentMonth CHAR;
  DECLARE MonthText CHAR;
  SET CurrentMonth = SUBSTRING(InputBody.Invoice.InvoiceDate FROM 6 FOR 2);

  SET MonthText = 
   CASE CurrentMonth
      WHEN '01' THEN 'January' 
      WHEN '02' THEN 'February'
      WHEN '03' THEN 'March' 
      WHEN '04' THEN 'April' 
      WHEN '05' THEN 'May' 
      WHEN '06' THEN 'June' 
      ELSE 'Second half of year' 
   END;

The following example shows a CASE function with a searched-when-clause. This example is still determined by one variable CurrentMonth:

 DECLARE CurrentMonth CHAR;
  DECLARE MonthText CHAR;
  SET CurrentMonth = SUBSTRING(InputBody.Invoice.InvoiceDate FROM 6 FOR 2);

  SET MonthText = 
   CASE  
      WHEN Month = '01' THEN 'January' 
      WHEN Month = '02' THEN 'February'
      WHEN Month = '03' THEN 'March' 
      WHEN Month = '04' THEN 'April' 
      WHEN Month = '05' THEN 'May' 
      WHEN Month = '06' THEN 'June' 
      ELSE 'Second half of year' 
   END;

In a searched-when-clause, different variables can be used in the WHEN clauses to determine the result. This is demonstrated in the following example of the searched-when-clause:

 DECLARE CurrentMonth CHAR;
  DECLARE CurrentYear CHAR;
  DECLARE MonthText CHAR;
  SET CurrentMonth = SUBSTRING(InputBody.Invoice.InvoiceDate FROM 6 FOR 2);
  SET CurrentYear = SUBSTRING(InputBody.Invoice.InvoiceDate FROM 1 FOR 4);

  SET MonthText = 
    CASE 
      WHEN CurrentMonth = '01' THEN 'January'
      WHEN CurrentMonth = '02' THEN 'February'
      WHEN CurrentMonth = '03' THEN 'March'
      WHEN CurrentYear = '2000' THEN 'A month in the Year 2000'
      WHEN CurrentYear = '2001' THEN 'A month in the Year 2001'
      ELSE 'Not first three months of any year or a month in the Year 2000 or 2001'
    END;