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!
WHEN 'C' THEN
CASE WHEN accountTypeID IN(1, 4, 7) THEN
[amount] * -1
WHEN 'D' THEN
CASE WHEN accountTypeID IN(2, 3, 6) THEN
[amount] * -1
CASE dataElement1 IN('asset', 'liability') THEN
END AS something
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.
Check out these BI Publisher tips including functions & calculations so you can understand more about the production and support of BI Publisher reports.
Ultimately the goal of commentary in OBIEE is to have a system for persisting feedback, creating a call to action, and recognizing the prolific users.