CASE Statements

Use case statements to express if/then logic. A case statement always has a pair of WHERE and THEN statements. CRM Analytics supports the simple and searched forms of case expressions in a SELECT statement. Case statements have two formats: simple and searched.

Simple Form 

A simple statement compares a case expression against a set of expressions. The result is the matched expression.

1SELECT Category, CASE Category
2WHEN 'Furniture' THEN 'Available'
3WHEN 'Office Supplies' THEN 'Unavailable'
4ELSE 'Unknown' END AS Availability
5FROM "Superstore"
6GROUP BY Category;

Here are the results.

CategoryAvailability
FurnitureAvailable
Office SuppliesUnavailable
TechnologyUnknown

Searched Form 

The searched form evaluates to a result. A searched statement compares an expression against a series of boolean expressions. If it matches a boolean expression, the result is the corresponding THEN clause. If the expression does not return true for any of the boolean expressions, then the result is the corresponding ELSE clause.

1SELECT Region, CASE
2WHEN sum(Profit) <= -500000 THEN 'Huge Loss'
3WHEN sum(Profit) <= 0 THEN 'Loss'
4WHEN sum(Profit) > 500000 THEN 'Huge Profit'
5ELSE 'Profit' END AS ProfitLoss
6FROM "Superstore"
7GROUP BY Region;
RegionProfitLoss
CentralProfit
EastProfit
SouthProfit
WestProfit

Introducing SQL for CRM Analytics
Quick Start: Run Your First Query
SQL vs SAQL
SQL Statements
SELECT Clause
FROM Clause
WHERE Clause
Boolean Expressions in SELECT Clause
GROUP BY Clause
GROUP BY ROLLUP
GROUPING()
HAVING Clause
ORDER BY Clause
LIKE Clause
BETWEEN Operator
LIMIT Clause
FETCH Clause
OFFSET Clause
CASE Statements
COALESCE()
NULLIF()
UNION Operator
Subquery
Window Functions
Date Functions
Aggregate Functions
Math Functions
String Functions and Operators
Release Notes
Previous Versions