CASE WHEN Statements

[intapiuser]

Overview

CASE WHEN statements provide great flexibility when dealing with buckets of results or when you need to find a way to filter out certain results. You can think of these almost as IF-THEN statements similar to other coding languages. Below you'll learn the proper syntax, where these can be placed in your query and a few examples that should help get you started. Happy Periscoping! 😊

Standard Syntax

When using a CASE WHEN statement, it's important to remember you need a condition, what to do when that condition is met, and an END clause. A simple example is below:

CASE
  WHEN  condition
    THEN result
  ELSE other_result
END

More often than not, you'll want multiple buckets, or have multiple conditions for your data! To add these, include more WHEN and THEN statements.

CASE
  WHEN  condition
    THEN result
  WHEN  condition_2
    THEN result_2
  ELSE other_result
END

Reminder: Remember to include one (and only one!) ELSE clause followed by your END statement. 

Organization

So where can these CASE WHEN statements be placed? Almost, anywhere! 

If you'd like to change some results in a column to different values, this can be done in the select statement. In this example, let's say North and South Dakota put aside their differences and became one Dakota. You could update the results of your state column with a CASE WHEN statement!

SELECT
  CASE
    WHEN state = 'North Dakota'
      THEN 'Dakota'
    WHEN state = 'South Dakota'
      THEN 'Dakota'
    ELSE state
  END

If you'd like to filter out specific results, but you have to depend on another value to know which results to filter, a CASE WHEN statement could be helpful in your WHERE clause! A great example of this can be found when using multiple Periscope filters in one query.

SELECT *
FROM table
WHERE
CASE
  WHEN 'default' IN ('[name_filter|default]')
    THEN [id_column=id_filter]
  WHEN 'default' IN ('[id_filter|default]')
    THEN [name_column=name_filter]
  ELSE [id_column=id_filter] OR [name_column=name_filter]
END

A full explanation of the above can be found here: filters-in-case-when-statements

Finally, CASE WHEN statements can be used to help you order rows in the proper way. Let's say in your state's data there are rows that have blank values for states! You can still order those in another way, for instance by city name.

SELECT *
FROM table
ORDER BY
(CASE
    WHEN state IS null
      THEN city
    ELSE state
END)

The above should help you get up and running with the CASE WHEN statement in no time! Which is your favorite way to use the CASE statement? Any creative use cases for this function while you have been working in Periscope? Post your thoughts below! 😊