Online Tech Support

This tutorial is based on examples to be easier to follow. The Oracle CASE expression allows to do comparison with values as for example IF expression does but Oracle Case would not need to be execute inside an additional procedure as it needs to be done with IF expression. The Oracle Case expression syntax are:

-- 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 you see 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 expression but you can use this style to write more complicated conditions and use different values to find matching.

The first 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 second Oracle Case query is with searched case expression and the condition is containing different value pairs as for example a_number is 1 and text should be “MySQL“. In this time the source query (SELECT 3 AS a_number, ‘Oracle’ AS text FROM dual) returns two value a number and a text and they are 3 and “Oracle“. The Case expression returns “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 );

In this following example we would like to show what happens when two values are correct in the Case conditions. The “source” query returns the same value (number 3) as in 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 output returns on the “one” text and ignores the “three” text. The reason is in the Oracle Case expression that takes first matching value from up to down and ignores rest of values.

 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 can see on the last output Oracle Case takes only the first matching condition and ignores the rest of values, so when you did a mistake in the value declaration the Case would not raise an error.

See Also:
Home Oracle SELECT