Case statement with multiple conditions

renee12345
renee12345 Member
in Beast Mode

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

Tagged:

Best Answer

  • RobSomers
    RobSomers Coach
    Answer ✓

    @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.

    **Was this post helpful? Click Agree or Like below**

    **Did this solve your problem? Accept it as a solution!**

Answers

  • RobSomers
    RobSomers Coach
    Answer ✓

    @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.

    **Was this post helpful? Click Agree or Like below**

    **Did this solve your problem? Accept it as a solution!**

Categories