Oracle Select Case Example

Oracle Select Case Example

Written by
Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.
  • SQL
  • 1 min read

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

NAMEJOBSALNEW_SALARY
ADAMSCLERK11001320
ALLENSALESMAN16001760
BLAKEMANAGER28503705
CLARKMANAGER24503185
FORDANALYST30003300