CASE WHEN

I ran into this today, and it looks really helpful for times when you need to have a certain value in a field which is based on (but not identical to) a value in one of your tables.

To illustrate: say you needed to pull data on a person which included whether or not said person was retired. The has “Retired” and “Working” as possible options in the “Retirement” field of the “People” table.

The Select query may look like this:

SELECT FirstName, LastName, DOB, Retirement 
FROM People;

But what if you were required to provide a code instead of “Retired” or “Working”? Maybe “Retired” needs to come back as “01” and “Working” needs to come back as “02.”

The way I would have dealt with this on my own is to make a tiny table with the codes and the retirement status in separate columns, then joined the table to the “People” table. But there’s another way – I think maybe a better way in terms of stress on the server’s CPU. (Is it better?)

It’s the “CASE WHEN” clause, and this is what it looks like:

SELECT FirstName,
                LastName,
                DOB,
                CASE
                          WHEN Retirement = ‘Retired’ THEN ’01’
                          WHEN Retirement = ‘Working’ THEN ’02’
                 END
                         AS RetirementStatus
FROM People;

I did find a real explanation of CASE modeanalytics.com – and it looks like it might be a good resource for learning SQL in general.

 

Advertisements

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s