Redshift CASE Statements: Syntax and Usage With 2 Easy Examples

|

Redshift Case Statements- Featured Image

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

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

  • An AWS Redshift Account.

Part 1: Understanding Amazon Redshift

Amazon Redshift: Redshift CASE Expression
Image Source: Nightingale HQ

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

Work with semistructured data using Amazon Redshift SUPER: Redshift CASE Expression Hevo Data
Image Source: AWS

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

Simplify Data Analysis with Hevo’s No-code Data Pipeline

Hevo Data, a No-code Data Pipeline helps to load data from any data source such as Salesforce, Databases, SaaS applications, Cloud Storage, SDKs, and Streaming Services and simplifies the ETL process. It supports 100+ data sources (including 30+ free data sources) and is a 3-step process by just selecting the data source, providing valid credentials, and choosing the destination. Hevo not only loads the data onto the desired Data Warehouse/destination like Amazon Redshift but also enriches the data and transforms it into an analysis-ready form without having to write a single line of code.

Get Started with Hevo for Free

Its completely automated pipeline offers data to be delivered in real-time without any loss from source to destination. Its fault-tolerant and scalable architecture ensure that the data is handled in a secure, consistent manner with zero data loss and supports different forms of data. The solutions provided are consistent and work with different BI tools as well.

Check out why Hevo is the Best:

  • Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled in a secure, consistent manner with zero data loss.
  • Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to the destination schema.
  • Minimal Learning: Hevo, with its simple and interactive UI, is extremely simple for new customers to work on and perform operations.
  • Hevo Is Built To Scale: As the number of sources and the volume of your data grows, Hevo scales horizontally, handling millions of records per minute with very little latency.
  • Incremental Data Load: Hevo allows the transfer of data that has been modified in real-time. This ensures efficient utilization of bandwidth on both ends.
  • Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.
  • Live Monitoring: Hevo allows you to monitor the data flow and check where your data is at a particular point in time.
Sign up here for a 14-Day Free Trial!

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.

Nicholas Samuel
Technical Content Writer, Hevo Data

Nicholas Samuel is a technical writing specialist with a passion for data, having more than 14+ years of experience in the field. With his skills in data analysis, data visualization, and business intelligence, he has delivered over 200 blogs. In his early years as a systems software developer at Airtel Kenya, he developed applications, using Java, Android platform, and web applications with PHP. He also performed Oracle database backups, recovery operations, and performance tuning. Nicholas was also involved in projects that demanded in-depth knowledge of Unix system administration, specifically with HP-UX servers. Through his writing, he intends to share the hands-on experience he gained to make the lives of data practitioners better.

No-code Data Pipeline for Amazon Redshift