CODEWITHSUNDEEP
t-sqlcase
phill
phill

Reputation: 13854

tsql : is it possible to do nested case statements in a select?

how do i incorporate a nested if statement in a select clause of a sql query? I know to use case when condition then X else y end but how do you do a nested one in the same fashion for each record in a record set. 

if x.boy is not null then 
   x.boy 
else if x.girl is not null
   then x.girl 
else if x.dog is not null 
   then x.dog
else 
    x.cat

here is my attempt: 

SELECT top 10
        id,
        case when x.boy <> NULL then 
            x.boy
        else case when  x.girl <> NULL 
            x.girl
                else case when  x.dog <> NULL 
            x.dog
                else x.cat 

        end as Who 
from house x

is this correct?

Upvotes: 17

Views: 27427

Answers (3)

Gabriel McAdams
Gabriel McAdams

Reputation: 58301

Yes. There is nothing wrong with a case within a case.

Although, here is your script, written corectly:

SELECT top 10
    id,
    case
        when x.boy IS NOT NULL then x.boy
        else case
            when x.girl IS NOT NULL THEN x.girl
            else case
                when x.dog IS NOT NULL THEN x.dog
                else x.cat
            end
        end
    end as Who 
from house x

OR

SELECT top 10
    id,
    case
        when x.boy IS NOT NULL then x.boy
        when x.girl IS NOT NULL THEN x.girl
        when x.dog IS NOT NULL THEN x.dog
        else x.cat
    end as Who 
from house x

OR

SELECT top 10
    id,
    coalesce(x.boy, x.girl, x.dog, x.cat) AS Who
from house x

Upvotes: 24

codingbadger
codingbadger

Reputation: 44024

Try this out:

SELECT top 10
        id,
        case when x.boy Is Not NULL then x.boy
        else 
              case when  x.girl Is Not NULL then x.girl
              else 
                    case when  x.dog Is Not Null Then x.dog
                    else x.cat End
               End

        end as Who 
from house x

although you could just use coalesce as Joe suggested.

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135848

You could simplify this with COALESCE.

SELECT TOP 10 id, COALESCE(x.boy, x.girl, x.dog, x.cat) as Who
    FROM house x

Upvotes: 17

Related Questions