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?
CASE can be used in any statement or clause that allows a valid expression. For example, you can use CASE in statements such as SELECT, UPDATE, DELETE and SET, and in clauses such as select_list, IN, WHERE, ORDER BY, and HAVING.
SQL Case Statement Syntax After that comes the keyword THEN and the value for that condition, like WHEN <condition> THEN <stuff> . This can then be followed by other WHEN / THEN statements. At the end you can add a value to use by default if none of the conditions are true with the ELSE keyword, as shown below.
In the Nested CASE expression, the outer statement executes until satisfying a condition. Once the condition is met, the inner CASE expression executes and returns a single result. If no condition is met in the outer statement, CASE expression returns the value in the ELSE statement.
Multiple conditions in CASE statementYou can evaluate multiple conditions in the CASE statement.
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
You could simplify this with COALESCE.
SELECT TOP 10 id, COALESCE(x.boy, x.girl, x.dog, x.cat) as Who
FROM house x
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With