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.
- CASE … WHEN
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.
- 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 statement is a little lighter version of CASE directive. You cannot implement a complex logic easily there and it usually serves as a “decoder”.
- 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