Geek Answers Handbook

CASE equivalent of a nested IIF statement

Can anyone decode the next nested IIF in a CASE statement in SQL. I know that IIF is allowed in SQL Server 2012, but it's hard for me to get an easy understanding of IIF nested logic. The following is my nested IIF expression

IIF(IIF(TABLE_A.Col_1 = 0, TABLE_A.Col_2 + (2*TABLE_A.Col_3), TABLE_A.Col_1)<=.5, 'A', 'B') AS Result

Any help is greatly appreciated.

0
source share
4 answers

This should be equivalent to:

 CASE WHEN CASE WHEN TABLE_A.Col_1 = 0 THEN TABLE_A.Col_2 + (2*TABLE_A.Col_3) ELSE TABLE_A.Col_1 END <= .5 THEN 'A' ELSE 'B' END As Result
+3
source 
 CASE WHEN (CASE WHEN TABLE_A.Col1= 0 THEN TABLE_A.Col2_2 + (2*TABLE_A.Col3) ELSE TABLE_A.Col1 END) <=0.5 THEN 'A' ELSE 'B' END AS result
+1
source

I think this is what comes down to a single CASE expression:

 CASE WHEN TABLE_A.Col_1 = 0 AND TABLE_A.Col_2 + (2*TABLE_A.Col_3) <= .5 THEN 'A' WHEN TABLE_A.Col_1 <> 0 AND TABLE_A.Col_1 <= .5 THEN 'A' ELSE 'B' END
0
source

It is already now, and there are other answers that already work, but for fun, you can write this as a functional expression without any CASE statements, for example:

 char(65 + ceiling(ceiling(COALESCE(NULLIF(TABLE_A.Col_1, 0), TABLE_A.Col_2 + (2*TABLE_A.Col_3))) - .5 / 10000000000000))

There is very little chance that the functional approach will improve markedly on large sets with good indexing.

Here is my test for proof script:

http://sqlfiddle.com/#!3/a95b3/2

0
source

More articles:


All Articles