Switch/case will be familiar to most developers, but did you know you could used them in your SQL statements as well?? I didn’t and here’s how you do it.

The syntax for this is pretty straightforward, but briefly take a look at the SQL statement below

SELECT TOP #arguments.recordcount# tbl1.someID, tbl1.adDisplayName, type =
CASE tbl1.someBannerType
WHEN 0 THEN ‘ValueClick’
WHEN 1 THEN ‘Internal’
ELSE ‘Not yet categorized’
END,
tbl1.someRanking, tbl1.someStartDateTime, tbl1.someDateAdded, tbl1.someDateUpdated, tbl1.someModifiedBy, tbl2.someCode, tbl3.someName
FROM tbl_2 tbl2
INNER JOIN tbl_1 tbl1 ON tbl2.someOtherID = tbl1.someOtherID
INNER JOIN tbl_3 tbl3 ON tbl2.someCode = tbl3.someCode
WHERE 1 = 1

The query was used for a banner ad component, so based on the Banner type being passed I wanted to dynamically set a type. If the value was 0 then the column type should display ValueClick, if 1 then it was an internally served advert. If the value passed was neither 0 or 1 then it should display another message.

As you can see the syntax differs somewhat from switch/case, but the principal is similar. CASE is equal to switch where tbl1.someBannerType is the value being evaluated. Where you would normally use case, you know have WHEN. So for each case if you have a WHEN x THEN something statement. The default case found if switch/case statements also exists and looks thus: ELSE something. And to conclude you’ll need to close that block and you do so by way of and END

And just as an afterthought, if you are using Oracle the function to use is DECODE and this is how you would apply it:

SELECT field1,
DECODE (field2,
100000, ‘Rich’,
1000000, ‘Richer’,
10000000, ‘Richest’)
FROM table