Conditional Expressions

In any programming or scripting language, we have to implement a decision logic. SQL is no different and Oracle comes with two easy directives how to handle it; they are conditional expressions.

1. CASE … WHEN
2. DECODE

CASE … WHEN

Not all data are categorized the way every user want them. To be able to do so, Oracle provides users with CASE directive. It works on the IF-THEN-ELSE basis. You can build a very complex logic with CASE statement.

Example:

• We need to create groups of employees per a specific salary range

SELECT id_employee
, id_department
, CASE
    WHEN salary <= 10000 THEN 'A'
    WHEN salary BETWEEN 10001 AND 20000 THEN 'B'
    WHEN salary BETWEEN 20001 AND 50000 THEN 'C'
    ELSE 'D'
  END as salary_class
FROM employee

• We need to create groups of employees per a specific salary range with a special category per department

SELECT id_employee
, id_department
, CASE
    WHEN id_department = 10 THEN
                              CASE
                                WHEN salary <= 10000 THEN '10_A'
                                WHEN salary BETWEEN 10001 AND 20000 THEN '10_B'
                                WHEN salary BETWEEN 20001 AND 50000 THEN '10_C'
                                ELSE '10_D'
                              END
    WHEN id_department = 20 THEN '20_A'    
    WHEN id_department IN (30,40) THEN
                                     CASE
                                        WHEN id_department = 30 AND salary BETWEEN 10000 AND 50000 THEN '30_A'
                                        WHEN id_department = 30 THEN '30_B'
                                        WHEN id_department = 40 AND salary BETWEEN 10000 AND 30000 THEN '40_A'
                                        WHEN id_department = 40 AND salary BETWEEN 30001 AND 50000 THEN '40_B'
                                        ELSE '40_C'
                                     END
    ELSE 'N/A'
  END as salary_class
FROM employee

All condition are evaluated one by one on a top-down basis. Whenever the condition is evaluated as true it ends an does not proceed further. This is very important to realize – the order matters.  If you set the very first condition too loose, it might never proceed further.

DECODE

DECODE statement is a little lighter version of CASE directive. You cannot implement a complex logic easily there and it usually serves as a “decoder”.

Example:

• there is no dimension table for departments and we need to incorporate it into our script.

SELECT id_department
, DECODE(id_department,         -- evaluated value
         1, 'IT',               -- IF id_department = 1 THEN 'IT' 
         2, 'Sales',            -- IF ... THEN ....
         3, 'Finance',
         4, 'CRM',
         'N/A') as dpt_name     -- ELSE...
FROM department