WebSphere Message Brokers
File: ak05600_
Writer: Bill Oppenheimer

Reference topic

This build: July 31, 2007 21:32:18

CASE function

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

Syntax

Both forms of CASE return a value depending on a set of rules defined in WHEN clauses.

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. The ELSE 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 clause version 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;
Related concepts
ESQL overview
Related tasks
Developing ESQL
Related reference
Syntax diagrams: available types
Complex ESQL functions
MOVE statement
Notices | Trademarks | Downloads | Library | Support | Feedback

Copyright IBM Corporation 1999, 2007Copyright IBM Corporation 1999, 2007. All Rights Reserved.
This build: July 31, 2007 21:32:18

ak05600_ This topic's URL is: