Redshift CASE Statements: Syntax and Usage With 2 Easy Examples

Redshift is a popular Cloud Service Database Provider for most Businesses. Businesses use it for the storage of their data. Redshift scales well to meet the changing storage needs of most Businesses. It also uses various mechanisms to ensure the stored data is secure. Redshift users also have access to tools that can help them to analyze their data.

When using Redshift, you will need to make decisions based on your data. This will require you to implement some logic in your queries. Redshift supports the Redshift CASE Statements that you can use to make decisions when interacting with your Redshift data. It provides you with great flexibility when you are dealing with many results or when there is a need to filter out particular results. 

In this article, we will be discussing the Redshift CASE Statements in detail. You will learn how to use it to filter your results and make decisions based on your data.

Here’s the outline of the article:

Table of Contents

• Prerequisites for Setting Redshift CASE Statements
• Part 1: Understanding Amazon Redshift
• Part 2: Understanding Redshift CASE Statements
  • Redshift CASE Expression Syntax
  • Redshift CASE Expression Examples
    • Redshift CASE Expression Example #1
    • Redshift CASE Expression Example #2
    • Redshift CASE Expression Example #3
    • Redshift CASE Expression Example #4
• Conclusion

Prerequisites for Setting Redshift CASE Statements

This is what you need for implementing Redshift CASE Statements in Redshift:

• An AWS Redshift Account.

Part 1: Understanding Amazon Redshift

Amazon Redshift is a Managed, Petabyte-Scale Cloud Data Warehouse Platform that makes the larger part of the AWS Cloud Platform. Amazon Redshift provides its users with a platform where they can store all their data and analyze it to extract deep business insights. 

Traditionally, Businesses had to make Sales Predictions and other Forecasts manually. Amazon Redshift does the largest part of the work of analyzing the data to give you time to focus on something else. It also gives you an opportunity to analyze your business data using the latest Predictive Analytics. 

This way, you can make smart decisions that can drive the growth of your Business. 

You can learn more about Amazon Redshift from the official documentation here. 

Part 2: Understanding Redshift CASE Statements

The Redshift CASE Statement is a Conditional Expression just like the If/Then/Else Statements used in other languages. It helps you to specify the result when you have many conditions. It can help you to take different actions based on different conditions in your data. 

Redshift CASE Expression Syntax

There are two types of Redshift CASE Statements, that are:

• Simple and 
• Searched. 

Simple CASE Expressions compare an expression with a value. If there is a match, the action specified in the THEN clause is executed. If no match is found, the action specified in the ELSE part is executed.

In Searched CASE Expressions, every CASE is evaluated based on a Boolean Expression. The CASE Statement returns the first CASE that matches the Boolean Expression. If no matching CASE is found from the WHEN clauses, the action specified in the ELSE part is executed.

The Simple Redshift CASE Expression takes the following syntax:

CASE expression
WHEN value THEN result
[WHEN...]
[ELSE result]
END

The Searched Redshift CASE Expression takes the following syntax:

CASE
WHEN boolean condition THEN result
[WHEN...]
[ELSE result]
END

The above parameters are described below:

• Expression: This can be a column name or a valid expression.
• Value: The value that expression will be compared with, like a character string or a numeric constant. 
• Result: The target expression or value is returned when a Boolean Condition or expression is evaluated. 
• Boolean Condition: A Boolean Condition is said to be valid or true when its value is equal to the constant. If it is true, the action specified in the THEN clause is executed. If it is false, the action specified in the ELSE part is executed. If no ELSE part is specified and the condition is false, the result is NULL.

Redshift CASE Expression Examples

When querying for data from Redshift, you may need to replace some values of a column with other values. You can do this in a SELECT Statement. 

Redshift CASE Expression Example #1

Let’s say you have a table named US_States. You may need to combine North Carolina and South Carolina into Carolina. This is demonstrated below:

SELECT
  CASE
    WHEN state = 'North Carolina'
      THEN 'Carolina'
    WHEN state = 'South Carolina'
      THEN 'Carolina'
    ELSE state 
  END FROM US_States;

The above CASE Statement will replace all occurrences of “North Carolina” and “South Carolina” with “Carolina”. The other state names will retain their names. 

When the state name is “North Carolina” or “South Carolina”, the corresponding THEN action, which is to replace it with “Carolina”, will be executed.

For the other state names, the ELSE part will be executed.

Redshift CASE Expression Example #2

Here is another example that uses the same table and column:

SELECT state,
 CASE state
     WHEN 'North Carolina'
       THEN 'Carolina' 
     WHEN 'South Carolina'
       THEN 'Carolina' 
     ELSE 'Other'
 END FROM US_States;

The statement will replace both “North Carolina” and “South Carolina” with “Carolina” and other state names with “Other”. 

Redshift CASE Expression Example #3

Now, suppose you have a Sales table and you need to group your Customers based on the amount that they have paid. You can use the Redshift CASE Statement as shown below:

SELECT amount,
  CASE WHEN amount<500 THEN 'Group 1'
       WHEN amount>500 THEN 'Group 2'
       ELSE 'Group 3'
  END FROM sales;

The above CASE Statement uses the amount column of table sales to group the users. It will put all Customers who have paid below 500 under Group 1 and those who have paid above 500 under Group 2. Customers who don’t belong to any of those two groups will be put under Group 3. 

You can also use the Redshift CASE Statement to order your table rows properly. Have a look at it in the upcoming example.

Redshift CASE Expression Example #4

Let’s use the US_States table to demonstrate this. Suppose there are rows in this table with blank values for the state column. You can order them in another way, for example, by city name as demonstrated below:

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

That is how easy it is to use the Redshift CASE Statement to query and produce desired results.

For more information on Redshift SubString Commands and Left and Right Functions, do check out our other article here.

Conclusion

This is what you’ve learnt in this article:

• You’ve learnt about Amazon Redshift.
• You’ve learnt about the Redshift CASE Statement.

While using AWS Redshift Services are insightful, it is a hectic task to set up and manage the proper environment on a regular basis. Extracting and integrating several heterogeneous sources into your Database like Amazon Redshift is also a big task. To make things easier, Hevo comes to your rescue. Hevo Data is a No-code Data Pipeline and has awesome 100+ pre-built Integrations that you can choose from.

Visit our Website to Explore Hevo

Hevo can help you integrate your data from numerous sources and load them into destinations like Redshift to Analyze real-time data with a BI tool such as Tableau. It will make your life easier and data migration hassle-free. It is user-friendly, reliable, and secure.

Sign Up for a 14-day free trial and see the difference!

Share your experience of learning about the Redshift CASE Statements in the comments section below.