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
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.
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;
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;
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;