case(...)

• SoQL Function Listing
• case(...)

The case(...) function is a special boolean function that can be used to return different values based on the result of boolean comparisons, similar to if/then/else statements in other languages.

Instead of a fixed number of parameters, case(...) takes a sequence of boolean, value pairs. Each pair will be evaluated to see if the boolean condition is true, and if so, the specified value will be returned. If no boolean condition can be evaluated as true, the function will return null.

For example, you can use case(...) with the CMS Open Payments General Payment Data to get a single column for name based on whether the payment was for a physician or a teaching hospital:

https://openpaymentsdata.cms.gov/resource/p2ve-2ws5.json?$select=CASE(covered_recipient_type = 'Covered Recipient Teaching Hospital', teaching_hospital_name, covered_recipient_type = 'Covered Recipient Physician', physician_first_name || ' ' || physician_last_name) AS name

The case(...) function is also very helpful in aggregations. In this case, we’ll count the number of disputed and undisputed payments for each physician in the state of Washington:

https://openpaymentsdata.cms.gov/resource/p2ve-2ws5.json?covered_recipient_type=Covered Recipient Physician&$select=physician_profile_id, physician_first_name, physician_last_name, sum(case(dispute_status_for_publication='No', 1, true, 0)) as no_count,sum(case(dispute_status_for_publication='Yes', 1, true, 0)) as yes_count&$group=physician_profile_id, physician_first_name, physician_last_name&recipient_state=WA

Note that in this case we wanted to make sure we returned 0 instead of null if we didn’t get a match, so the final pair passed into case(...) was true, 0.