How are multiple case statements used in SQL?

Using the CASE WHEN (with no expression between CASE and WHEN) syntax for a CASE expression, the pattern is:

CASE WHEN <boolean expression> THEN <result> [ELSE <result>] END.

In this case (excuse the pun) the <result> can literally be any expression, for example a CASE expression, to look something like

CASE WHEN <boolean expression> THEN CASE WHEN <boolean expression> THEN <result> [ELSE <result>] END [ELSE <result>] END.

It is thus possible to effectively ‘multiply’ the branches a single value could take in this way.

But, just to keep the madness, each result within the inner CASE statements could also be a CASE statement, and so it goes.

For example, to determine if a given year is a leap year one way to do it could be:

CASE WHEN @YEAR%4 = 0
     THEN CASE WHEN @YEAR%100 = 0
               THEN CASE WHEN @YEAR%400 = 0
                         THEN 'leap'
                         ELSE 'non-leap'
                    END
               ELSE 'leap'
           END
    ELSE 'non-leap'
END

This can be a really useful feature but can get confusing quite easily, especially if the code isn’t formatted suggestively or correctly. Often there is no choice but to use this type of CASE statement, but often a little refactoring can make the expression more readable. For example the above statement unnecessarily uses this construct, when it could have been more concisely and readably written as:

CASE WHEN @YEAR%100 = 0 AND @YEAR%400 = 0 
          OR @YEAR%100 <> 0 AND @YEAR%4 = 0 
     THEN 'leap' ELSE 'non-leap' 
END

#pedantry: this is actually an example of nested rather than multiple CASE expressions. Multiple case statements would simply be comma separated CASE expressions, but I assume you were asking about the former.

hi,

Other than what Trevor said, you could use Decode as well if you are using multiple case statements over a single column.

Syntax: DECODE(COLUMN,INP1,OUT1,INP2,OUT2,DFLT)

For e.g;

if its 0, then its ‘low’. If its 1, then its ‘high’. If its any number other than 0 or 1, then ‘NA’.

SELECT DECODE(SAMPLE_COL,'0','LOW','1','HIGH','NA') AS COL_USNG_DCD
FROM TABLE;

Using Case , it will be:

SELECT  CASE WHEN SAMPLE_COL = '0' THEN 'LOW'
     CASE WHEN SAMPLE_COL = '1' THEN 'HIGH'
  ELSE 'NA'
  END AS COL_USNG_CASE
FROM TABLE;