How to use 'Case' statement in Oracle

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

Let’s take few scenarios to Implement Case Statement:

I have taken the below data to explain Case Statement:

ID	FIRST_NAME	LAST_NAME	START_DATE	END_DATE	SALARY	CITY	DESCRIPTION
01	Jason	Martin	07/25/1996 00:00:00	07/25/2006 00:00:00	1,235	Chandigarh	Developer
02	Alison	Mathews	03/21/1976 00:00:00	02/21/1986 00:00:00	6,662	Delhi	Lead
03	James	Smith	12/12/1978 00:00:00	03/15/1990 00:00:00	6,545	Vancouver	Tester
04	Celia	Rice	10/24/1982 00:00:00	04/21/1999 00:00:00	2,345	Sydney	Manager
05	Robert	Black	01/15/1984 00:00:00	08/08/1998 00:00:00	2,335	Pune	Manager
06	Linda	Green	07/30/1987 00:00:00	01/04/1996 00:00:00	4,323	New York	Tester
07	David	Larry	12/31/1990 00:00:00	02/12/1998 00:00:00	7,898	Sydney	Developer
08	James	Cat	09/17/1996 00:00:00	04/15/2002 00:00:00	1,233	Vancouver	Tester

Scenario 1: fetch the salary as High Salary when it us greater > 2000 else Low salary.

SELECT first_name

     , last_name

     , salary

     , CASE WHEN salary < 2000 THEN 'Low Salary ' ELSE 'High Salary' END

          AS salary_level

  FROM EMPLOYEE ;

FIRST_NAME	LAST_NAME	SALARY	SALARY_LEVEL
Jason	Martin	1,235	Low Salary
Alison	Mathews	6,662	High Salary
James	Smith	6,545	High Salary
Celia	Rice	2,345	High Salary
Robert	Black	2,335	High Salary
Linda	Green	4,323	High Salary
David	Larry	7,898	High Salary
James	Cat	1,233	Low Salary

Scenario 2 :

Get the list of Managers who have stayed in the company for more than 10 years

and for other designated employees, get the list if they have stayed for less than 10 years.

SELECT first_name

     , last_name

     , description

     , FLOOR (MONTHS_BETWEEN (end_date, start_date) / 12) years

  FROM EMPLOYEE

 WHERE CASE

          WHEN     UPPER (description) = 'MANAGER'

               AND FLOOR (MONTHS_BETWEEN (end_date, start_date) / 12) >= 10

          THEN

             1

          WHEN     UPPER (description) <> 'MANAGER'

               AND FLOOR (MONTHS_BETWEEN (end_date, start_date) / 12) <= 10

          THEN

             1

          ELSE

             0

       END = 1

FIRST_NAME	LAST_NAME	DESCRIPTION	YEARS
Jason	Martin	Developer	10
Alison	Mathews	Lead	9
Celia	Rice	Manager	16
Robert	Black	Manager	14
Linda	Green	Tester	8
David	Larry	Developer	7
James	Cat	Tester	5