Oracle Select Case Example

In Oracle, the CASE statement is used to perform the if..else condition in the SQL query. Below is an Oracle Select Case example:

CASE Statement Syntax

CASE [ expression ]

   WHEN condition_1 THEN result_1
   WHEN condition_2 THEN result_2
   ...
   WHEN condition_n THEN result_n

   ELSE result

END

Oracle Select Case Example

The following Oracle SQL query calculates the new salary depending on the job.

SELECT
  ename,
  job,
  SAL,
  CASE job
    WHEN 'MANAGER'
    THEN SAL + (SAL * 30/100)
    WHEN 'CLERK'
    THEN SAL + (SAL * 20/100)
    ELSE SAL + (SAL * 10/100)
  END new_salary
FROM
  EMP
ORDER BY
  ENAME;

Output

NAME	JOB	SAL	NEW_SALARY
ADAMS	CLERK	1100	1320
ALLEN	SALESMAN	1600	1760
BLAKE	MANAGER	2850	3705
CLARK	MANAGER	2450	3185
FORD	ANALYST	3000	3300