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