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?
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