xmetricFunctions

Tableau function Logical

Tableau CASE to SQL

CASE [field] WHEN value1 THEN result1 ... ELSE default END compares the field with each WHEN value and returns the first match, or the ELSE value; without ELSE it returns null. A null field matches nothing and goes to ELSE. SQL's simple CASE field WHEN ... works the same in DuckDB, Snowflake and BigQuery. WHEN IN (...) becomes a searched CASE WHEN field IN (...).

Updated · Checked on the xmetric engine

What is the syntax?

CASE <expression>
WHEN <value1> THEN <then1> [WHEN ...] [ELSE <default>]
END
expression
the value to compare, usually a field.
value
a value to match, or IN (...) with a list or set.
default
optional, returned when nothing matches (and when the expression is null).

Returns the THEN value of the first matching WHEN, the ELSE value, or null if nothing matches and there is no ELSE.

What does it return, and what is the SQL?

Each example runs on the same 6 sample rows twice: the Tableau formula on the xmetric engine, and the SQL in DuckDB.

Which shoe goes with each season?

Tableau

CASE [Season]
WHEN 'Summer' THEN 'Sandals'
WHEN 'Winter' THEN 'Boots'
ELSE 'Sneakers'
END

SQL (DuckDB)

SELECT
  CASE Season
    WHEN 'Summer' THEN 'Sandals'
    WHEN 'Winter' THEN 'Boots'
    ELSE 'Sneakers'
    END AS result
FROM input
ORDER BY id

Spring has no WHEN, so it gets Sneakers, and so does row 5, which has no season.

SeasonResult
SummerSandals
WinterBoots
SpringSneakers
SummerSandals
Sneakers
WinterBoots

The Tableau formula and the SQL return the same 6 rows.

Where does a straight translation go wrong?

Common mistake

Why do blank seasons get boots when the default is checked first?

Rewriting the CASE to test the default first, WHEN Season NOT IN (...), gives a null test for row 5. A null test is not true, so it falls through to the last branch, Boots. Tableau gives it the ELSE value, Sneakers.

Tableau

CASE [Season]
WHEN 'Summer' THEN 'Sandals'
WHEN 'Winter' THEN 'Boots'
ELSE 'Sneakers'
END

Tempting SQL

SELECT
  CASE
    WHEN Season NOT IN ('Summer', 'Winter') THEN 'Sneakers'
    WHEN Season = 'Summer' THEN 'Sandals'
    ELSE 'Boots'
    END AS result
FROM input
ORDER BY id

SQL that matches

SELECT
  CASE Season
    WHEN 'Summer' THEN 'Sandals'
    WHEN 'Winter' THEN 'Boots'
    ELSE 'Sneakers'
    END AS result
FROM input
ORDER BY id
SeasonTableauTempting SQLSQL that matches
SummerSandalsSandalsSandals
WinterBootsBootsBoots
SpringSneakersSneakersSneakers
SummerSandalsSandalsSandals
SneakersBootsSneakers
WinterBootsBootsBoots

The SQL that matches returns the Tableau result on every row; the marked cells are where the tempting SQL differs.

What do CASE without ELSE and CASE WHEN IN return?

Without ELSE, unmatched rows are null. WHEN IN matches a list and becomes a searched CASE in SQL. Columns 1 to 6 are the sample rows, by id.

CaseTableau, then SQL (DuckDB)123456
no ELSECASE [Season] WHEN 'Summer' THEN 'Sandals' WHEN 'Winter' THEN 'Boots' ENDCASE Season WHEN 'Summer' THEN 'Sandals' WHEN 'Winter' THEN 'Boots' ENDSandalsBootsnullSandalsnullBoots
WHEN INCASE [Region] WHEN IN ('East', 'West') THEN 'Coast' ELSE 'Inland' ENDCASE WHEN Region IN ('East', 'West') THEN 'Coast' ELSE 'Inland' ENDCoastCoastCoastCoastInlandInland

On every row, each Tableau formula and its SQL return the same value.

How do I write it in Snowflake or BigQuery?

The first example, written for each warehouse from its documentation. These are not run here; DuckDB is.

Snowflake

SELECT
  CASE Season
    WHEN 'Summer' THEN 'Sandals'
    WHEN 'Winter' THEN 'Boots'
    ELSE 'Sneakers'
    END AS result
FROM input
ORDER BY id

Snowflake documentation

BigQuery

SELECT
  CASE Season
    WHEN 'Summer' THEN 'Sandals'
    WHEN 'Winter' THEN 'Boots'
    ELSE 'Sneakers'
    END AS result
FROM input
ORDER BY id

BigQuery documentation

What does your own CASE formula return?

Edit the formula and run it on the 6 sample rows. Fields: [id], [Region], [Season], [Score], [Flag], [Backup].

Runs in your browser on the xmetric engine. Nothing is uploaded.

Which functions go with it?

What do people ask about CASE?

What happens when the field is null?

It matches no WHEN, so it gets the ELSE value; row 5 has no season and gets Sneakers. Without ELSE it is null.

Can CASE test ranges, like "> 80"?

No, CASE only matches values. For ranges use IF ... ELSEIF ... END, which becomes a searched CASE WHEN in SQL.

Is CASE faster than IF?

They return the same results. CASE is easier to read when you compare one field with many values.

Where is this documented?