Case statement with multiple conditions

renee12345

currently I have this query and I have 16 exclusion reasons. Is there a more efficient way to write this beastmode?

CASE WHEN exclusion reason 1 IS NULL THEN
CASE WHEN exclusion reason 2 IS NULL THEN
CASE WHEN exclusion reason 3 IS NULL THEN
CASE WHEN exclusion reason 4 IS NULL THEN
CASE WHEN exclusion reason 5 IS NULL THEN
CASE WHEN exclusion reason 6 IS NULL THEN
CASE WHEN exclusion reason 7 IS NULL THEN
CASE WHEN exclusion reason 8 IS NULL THEN
CASE WHEN exclusion reason 9 IS NULL THEN
CASE WHEN exclusion reason 10 IS NULL THEN

'other' END END END END END END END END END

RobSomers

@renee12345 It looks like you're just trying to find the first exclusion reason that isn't null. You'll want use the COALESCE() function:

COALESCE('exclusion reason 1','exclusion reason 2',…)

COALESCE looks through the first field listed and if it's null, moves on to the next field, and repeats the process until it finds a non-null value.

RobSomers

@renee12345 It looks like you're just trying to find the first exclusion reason that isn't null. You'll want use the COALESCE() function:

COALESCE('exclusion reason 1','exclusion reason 2',…)

COALESCE looks through the first field listed and if it's null, moves on to the next field, and repeats the process until it finds a non-null value.