SQL CASE and ELSE – Usage and Examples

case

Hello I am considering the following:

CASE 
   WHEN xyz.something = 1 THEN 'SOMETEXT'
   WHEN xyz.somethingelse = 1 THEN 'SOMEOTHERTEXT'
   WHEN xyz.somethingelseagain = 2 THEN 'SOMEOTHERTEXTGOESHERE'
   ELSE 'SOMETHING UNKNOWN'

Is this effectively doing a ELSE in between two WHEN, ie xyz.something = 1 has priority over xyz.somethingelse = 1 ?

If not, is this the right approach?

CASE WHEN xyz.something = 1 THEN 'SOMETEXT' ELSE 
CASE WHEN xyz.somethingelse = 1 THEN 'SOMEOTHERTEXT' ELSE 
CASE WHEN xyz.somethingelseagain = 2 THEN 'SOMEOTHERTEXTGOESHERE'
       ELSE 'SOMETHING UNKNOWN'

Best Answer

The CASE expression is evaluated from top to bottom, if no WHEN is matched ELSE is used. You need an END for each CASE. Your first expression:

CASE WHEN xyz.something = 1 THEN 'SOMETEXT'
     WHEN xyz.somethingelse = 1 THEN 'SOMEOTHERTEXT'
     WHEN xyz.somethingelseagain = 2 THEN 'SOMEOTHERTEXTGOESHERE'
     ELSE 'SOMETHING UNKNOWN'
END

is similar to:

if xyz.something == 1:
    return 'SOMETEXT'
else if xyz.somethingelse = 1:
    return 'SOMEOTHERTEXT'
...
else:
    return 'SOMETHING UNKNOWN'

Your second expression:

CASE WHEN xyz.something = 1 THEN 'SOMETEXT' 
     ELSE CASE WHEN xyz.somethingelse = 1 THEN 'SOMEOTHERTEXT' 
               ELSE CASE WHEN xyz.somethingelseagain = 2 THEN 'SOMEOTHERTEXTGOESHERE'
                         ELSE 'SOMETHING UNKNOWN'
                    END
          END
END

is similar to:

if xyz.something == 1:
    return 'SOMETEXT'
else:
    if xyz.somethingelse = 1:
        return 'SOMEOTHERTEXT'
    else:
        if ...

        else:
            return 'SOMETHING UNKNOWN'

Both expressions evaluate to one string.

Not sure if this answers your question, can you elaborate on what you are trying to express?

Related Question