Art of BI: SQL Server CASE IN() Statement Revealed- Who Knew?
Author: Christian Screen | | July 1, 2009
I am about to show you a great sql trick that only a few SQL Server T-SQL masters actually know. It will save you a decent amount of coding and make you the envy of your Sql Server colleagues.
Even in the Microsoft documentation on the CASE statement does not list that the CASE … WHEN…THEN…END statement can actuall not only check for logical boolean expressions but via operators but it can also handle an IN() statement similar to a fullblown SQL (or SQL Sub-Query statement, if so inclinded) function in the where clause. The code is quite simple. I have not found any restrictions when using constants (the hard-coded text that does not change) or immediate variables that are defined within the execution frame of the CASE IN() statment such as CASE WHEN something IN(@supertest). That is where @supertest = ‘A, B, C’.
Here is an decent example so enjoy and share with your friends. Be sure to comment on this if you like it. We are trying to gain a rep here at ArtOfBi.com!
SELECT
accountID,
accountSubID,
CASE [debitOrCredit]
WHEN 'C' THEN
CASE WHEN accountTypeID IN(1, 4, 7) THEN
[amount] * -1
ELSE
[amount]
END
WHEN 'D' THEN
CASE WHEN accountTypeID IN(2, 3, 6) THEN
[amount] * -1
ELSE
[amount]
END
ELSE
[amount]
END
AS amount
,
CASE dataElement1 IN('asset', 'liability') THEN
'Budget Account'
ELSE
'Income Account'
END AS something
FROM
FactDataTable
I have not seen any performance issues with the use of this case statement. It works like a charm for me on tables with 10,000+ records. As a side note, using the CASE IN() statement requires only two rigid assumptions:
- The IN statement must be immediately next to the parenthesis (i.e. no space between the two) ex: IN(‘yes’, ‘no’) and not IN (‘yes’, ‘no’)
- The values must be separated by a comma. If the value is a string it must be contained as a string in single-tick quotes.
Related Posts
Oracle BI Publisher (BIP) Tips: Functions, Calculations & More
Check out these BI Publisher tips including functions & calculations so you can understand more about the production and support of BI Publisher reports.
How to Index a Fact Table – A Best Practice
At the base of any good BI project is a solid data warehouse or data mart.
Qlik vs. Tableau vs. Power BI: Which BI Tool Is Right for You?
Tableau, Power BI, and Qlik each have their benefits. What are they and how do you choose? Read this blog post for a quick analysis.