Thursday, November 1, 2012

IF ELSE statement in oracle SELECT QUERY

Every time I use and forget the semantics of following two sql queries. And I think the queries are self explanatory.
SELECT emp_name, 
    CASE WHEN emp_type = 0 THEN 'Temporary'
         WHEN emp_type > 0 AND emp_type < 5 THEN 'Permanent'
         ELSE 'UNKNOWN'
    END AS emp_type
FROM employee;
SELECT emp_name, 
    DECODE(emp_type, 0, 'Temporary',
                     1, 'Permanent',
                     2, 'Permanent',
                     3, 'Permanent',
                     4, 'Permanent',
           'UNKNOWN')AS emp_type
FROM employee;