Case

Description

Tests the values of a column or expression and returns values based on the results of the test.

Syntax

Case ( column WHEN value1 THEN result1 { WHEN value2 THEN result2 
	{ ... } } { ELSE resultelse } )

Argument	Description
column	The column or expression whose values you want to test. Column can be the column name or the column number preceded by a pound sign (#). Column can also be an expression that includes a reference to the column. Column is compared to each valuen.
WHEN (optional)	Introduces a value-result pair. At least one WHEN is required.
valuen	One or more values that you want to compare to values of column. A value can be: A single value A list of values separated by commas (for example, 2, 4, 6, 8) A TO clause (for example, 1 TO 20) IS followed by a relational operator and comparison value (for example, IS>5) Any combination of the above with an implied OR between expressions (for example, 1,3,5,7,9,27 TO 33, IS>42)
THEN	Introduces the result to be returned when column matches the corresponding valuen.
resultn	An expression whose value is returned by Case for the corresponding valuen. All resultn values must have the same datatype.
ELSE (optional)	Specifies that for any values of column that do not match the values of valuen already specified, Case returns resultelse.
resultelse	An expression whose value is returned by Case when the value of column does not match any WHEN valuen expression.

Returns

The datatype of resultn. Returns the result you specify in resultn.

Usage

If more than one WHEN clause matches column, Case returns the result of the first matching one.

Examples

Example 1

This expression for the Background.Color property of a Salary column returns values that represent red when an employee’s salary is greater than $70,000, green when an employee’s salary is greater than $50,000, and blue otherwise:

Case(salary WHEN IS >70000 THEN RGB(255,0,0) WHEN IS
>50000 THEN RGB(0,255,0) ELSE RGB(0,0,255))

Example 2

This expression for the Background.Color property of an employee Id column returns red for Id 101, gray for Id 102, and black for all other Id numbers:

Case(emp_id WHEN 101 THEN 255 WHEN 102 THEN
RGB(100,100,100) ELSE 0)

Example 3

This expression for the Format property of the Marital_status column returns Single, Married, and Unknown based on the data value of the Marital_status column for an employee:

Case(marital_status WHEN 'S'THEN 'Single' WHEN 'M' THEN
'Married' ELSE 'Unknown')