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:
|
|
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. |
Return value
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
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))
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)
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')
See also
