CASE Expression Examples

CASE expressions are used to evaluate a condition or conditions and return a value. I’ve most often seen this in SELECT statements but that’s not a limit. In fact, documentation states that CASE can be used in any statement or clause that allows a valid expression. I used it differently than SELECT this week in a way we’ll look at below.

Weather for the Week

Let’s create a small set of test data to work with:

CREATE TABLE Temperatures
	T_Day varchar(20),
	T_Temp smallint

INSERT INTO Temperatures
VALUES ('Monday',30),('Tuesday',40),('Wednesday',60),('Thursday',70),('Friday',70);

Taking a look at our temperatures for the work week, we have this forecast of temperatures:

SELECT * FROM Temperatures;

What if we don’t want to look at the numbers? Numbers can be hard. We can use CASE to make it simple and just return Cold, Chilly, Warm, or Hot:

	, FeelsLike = CASE 
			WHEN T_Temp < 50 THEN 'COLD'
			WHEN T_Temp >= 50 AND T_Temp < 60 THEN 'CHILLY'
			WHEN T_Temp >= 60 AND T_Temp < 75 THEN 'WARM'
FROM Temperatures;

Updating with CASE

That all looks great but what if we find out that something went wrong with our weather model? Any temperatures less than 70 are off by 5 degrees and temperatures 70 or above are off by 10 degrees. Can we use CASE to update our data? You bet we can. We can run this script to get our data updated:

UPDATE Temperatures
		WHEN T_Temp < 70 THEN T_Temp + 5
		ELSE T_Temp + 10

If we check our Temperatures table again we can confirm that our temperatures have been adjusted appropriately:

SELECT * FROM Temperatures;

Running our first SELECT with a case expression from above, we see the end of the week is actually going to be HOT:

Looks like a beautiful end to the week.

CASE for Divide by Zero

CASE expressions are also useful when it comes to handling divide by zero errors. I touched on this and a few other options for Divide by Zero errors on this blog post a few weeks ago if you want to check it out.

Thanks for reading!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s