The CASE statement goes through conditions and returns a value when the first condition is met (like an IF-THEN-ELSE statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause. If there is no ELSE part and no conditions are true, it returns NULL.
CASE syntax
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END
Note that ELSE result is optional and if not used and no conditions are true, it returns NULL.
Example(s)
Example 1 - Output "Yes" when Today's date is greater than or equal to the Assignment Start Date, else output "No".
CASE
WHEN [Assignment: Start Date] <= GETDATE() THEN ‘Yes’
ELSE ‘No’
END
Example 2 - Output 'Assignee' when the assignment type does not start with 'Compliance Only'
CASE
WHEN [Assignment: Relocation Policy] NOT LIKE '%Compliance%' THEN 'Assignee'
END
Example 3 - Outputs Cost Estimate Line item amounts when formula is true
CASE
WHEN [Cost Estimate Line Item View: Line Item Name] IN ('Hypothetical Home Country Income Tax','Hypothetical Home Country State Tax','Hypothetical Home Country Social Security')
THEN ([Cost Estimate Line Item View: Company Amount Total])
END
Example 4 - Outputs the email address of Relocation Coordinators
CASE
WHEN [Resource Role Association: Resource Role]= 'Relocation Coordinator' THEN [Resource: Email]
END