The CASE statement in SQL is like a series of “IF-THEN” statements in Salesforce formula fields. It lets you set conditions and outcomes within your query. For example, you might use CASE to categorize accounts based on their revenue, much like you might use a formula field to categorize leads in Salesforce.
The good news is if you have created a CASE statement within a Salesforce formula, you can re-use that knowledge in SQL.
WHEN <condition1> THEN <result1>
[ WHEN <condition2> THEN <result2> ]
[ ... ]
[ ELSE <result3> ]END
LAB:
Let’s work on an existing SQL project from an existing lab and enhance it with a case statement:
In a previous lab, we used the WHERE clause to filter our leads by country and number of locations. What if we wanted to classify and label these locations alongside others instead of filtering our data? A WHERE clause is a simple and efficient way to label data when there is a small number of labels.
SELECT *,
CASE
WHEN "NUMBEROFLOCATIONS__C" >1 AND "COUNTRY" = 'USA' THEN 'US Muli-Location'
WHEN "NUMBEROFLOCATIONS__C" =1 AND "COUNTRY" = 'USA' THEN 'US Single Location'
ELSE 'Other' END "Location Classification"
FROM SFDC.STAGE.LEAD;