Home  >  Oracle SQL & PL/SQL  >  CASE Expression in an Oracle SELECT statement

Note: The article ( named CASE Expression in an Oracle SELECT statement ) was taken from www.in-oracle.com.

The CASE expression is a more complex version of the DECODE function.

This case of CASE use is another way to "rewrite" a DECODE function:

SELECT ename, empno,
              (  CASE deptno
                 WHEN 10  THEN 'Accounting'
                 WHEN 20  THEN 'Research'
                    ELSE 'Unknown'
                END ) department
FROM emp
ORDER BY ename;

is another way of writing:

SELECT ename, empno,
               DECODE (deptno,
                                       10, 'Accounting',
                                       20, 'Research',
                                             'Unknown' ) department
FROM emp
ORDER BY ename;

Note: In this case the execution plan doesn't change.

Using the CASE function, multiple conditions can be combined into one:

SELECT ename, empno,
               (CASE
                   WHEN sal < 2500 THEN 'Low'
                   WHEN sal >= 2500 THEN 'High'
                END) salary
FROM emp
ORDER BY ename;

Note: The article ( named CASE Expression in an Oracle SELECT statement ) was taken from www.in-oracle.com.