0

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'
4

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?

  • thank you for answer. So if the CASE expression is evaluated from top to bottom, it seems to me that both expressions are resulting in the same logical output. I want to use the simplest (and fastest) code, which might the expression 1? – RockScience Jan 2 '18 at 10:09
  • I would not expect much performance gain, but I would prefer the first one. – Lennart Jan 2 '18 at 10:26
  • The point of a CASE really is that it is much simpler to read than a series of if/else if/else statements. Logically they are the same but internally they will get translated to much the same. – Phil Sumner Jan 2 '18 at 13:29
  • The pseudo code was just to demonstrate semantics for the OP, there are no IF THEN ELSE construct in SQL (unless procedural parts of the language are included). – Lennart Jan 2 '18 at 13:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.