How To Use CASE Expression In Oracle

This online help page is based on examples to make it easier to follow. Oracle CASE expression allows you to do comparison with values. For example the IF expression does let you to do the same but it needs to be done inside an additional procedure. Oracle CASE would not need to that and you can write it into your SQL query. There are two types of syntax for Oracle CASE and they are following:

-- 1. simple case expression
CASE <value_to_compare> 
     WHEN <value_to_compare_with> THEN <return_value> 
   [ WHEN <value_to_compare_with_2> THEN <return_value_2>
     ....
     ELSE <other_value> ] END

-- 2. searched case expression
CASE WHEN <the_conditions>   THEN <return_value> 
   [ WHEN <the_conditions_2> THEN <return_value_2>
     ....
     ELSE <other_value> ] END

As we got two CASE expressions and first of them the simple CASE expression lets you to do a simple conditions to find a match. The searched CASE expression looks almost the same as the first but you can use this style to write more complicated conditions and use different values to find matching.

The following Oracle CASE query is written with simple CASE expression and the source query (SELECT 3 AS a_number FROM dual) returns only 1 row with value 3. The CASE expression needs to find in the list “WHEN 3” and return text “three“.

 SELECT
    CASE a_number
        WHEN 0
        THEN 'zero'
        WHEN 1
        THEN 'one'
        WHEN 2
        THEN 'two'
        WHEN 3
        THEN 'three'
        WHEN 4
        THEN 'four'
    END AS number_in_a_word
   FROM (SELECT 3 AS a_number
          FROM dual );

The next Oracle CASE query is with searched CASE expression and the condition is containing different value pairs to check as for example a_number is 1 and text should be “MySQL“. In this example the source query (SELECT 3 AS a_number, ‘Oracle’ AS text FROM dual) returns two value – a number and a text. They values are “3” and “Oracle“. The CASE expression should return “The number is three and the text is Oracle” as this is the returning value for the second condition (WHEN a_number = 3 AND text = ‘Oracle’).

 SELECT
    CASE
        WHEN a_number = 1 AND text = 'MySQL'
        THEN 'The number is one and the text is MySQL'
        WHEN a_number = 3 AND text = 'Oracle'
        THEN 'The number is three and the text is Oracle'
        WHEN a_number = 10 AND text = 'MsSQL'
        THEN 'The number is ten and the text is MsSQL'
        ELSE 'Unknown number and text'
    END AS number_in_a_word
   FROM (SELECT 3 AS a_number, 'Oracle' AS text
          FROM dual );

On this third example we would like to show what happens when two values have matching in the same CASE condition. The “source” query returns the same value (number 3) as on the first example but we do have set two “WHEN 3” conditions. First of them returns text “one” and the second “three“. Take a look at the example below – the output returns the “one” text and ignores text “three“. The reason is in the Oracle CASE expression that takes the first matching value from up to down and ignores rest of them.

 SELECT
    CASE a_number
        WHEN 0
        THEN 'zero'
        WHEN 3
        THEN 'one'
        WHEN 2
        THEN 'two'
        WHEN 3
        THEN 'three'
        WHEN 4
        THEN 'four'
        ELSE 'unknown'
    END AS number_in_a_word
   FROM (SELECT 3 AS a_number
          FROM dual );

As you see on the last output Oracle CASE takes only the first matching condition and ignores the rest, so when you did a mistake in the value declaration the CASE would not raise any error.

See Also:
Oracle Home