CODEWITHSUNDEEP
sqlsql-servert-sql
Neeraj Bhanot
Neeraj Bhanot

Reputation: 57

Using Nested Case Expression

I am stuck in properly using Nested Case Expression.

enter image description here

The requirement is to create the calculated column that will be either 0 or 1. If the name is 'abc' and ind = 1, then the value of calc column will be 1 . But if the name is 'abc' and salary is > 2000 . then the value will be 1 again irrespective the value of ind.

I have written the below query using OR condition in CASE Expression but want to use the Nested CASE. 

select t.*,  case when (name = 'abc' and ind = 1 ) or (name = 'abc' and sal > 2000) 
                  then 1 
                  else 0 
                  end 
from test t

Below is the result:

enter image description here

Upvotes: 1

Views: 66

Answers (4)

Michael Bruesch
Michael Bruesch

Reputation: 660

If you HAVE to have nested case expression then this is how you would write it:

SELECT t.*, 
    CASE 
        WHEN name = 'abc' THEN
            CASE 
                WHEN ind = 1 THEN 1 
                WHEN sal > 2000 THEN 1 
                ELSE 0
            END 
        ELSE 0
    END
FROM test t;

Upvotes: 1

GGadde
GGadde

Reputation: 391

If you are on SQL Server 2012 and above, you can also use an IIF statement like below:

select iif(name = 'abc' AND (salary > 2000 OR ind = 1), 1,0)

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

I don't know why you need nested case expression, i would do instead of nested case expression that would be more efficient : 

select t.*, (case when name = 'abc' and (ind = 1 or sal > 2000) 
                  then 1 
                  else 0 
             end) 
from test t;

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 176324

You don't need nested CASE:

-- multiple conditions
select t.*, case when name = 'abc' and ind = 1 then 1
                 when name = 'abc' and sal > 2000 then 1 
                 else 0 end 
from test t

Upvotes: 2

Related Questions