Usage of CASE Statement in Oracle SQL
Case is similar to a switch case used to implement IF-THEN-ELSE.
Switch Case used in (EBS): IF-THEN-ELSE
E-Business Suite(older of ERP)-onpremise(local server)
Oracle Fusion Cloud(new version of ERP)-Cloud Server placed in Data Center
Syntax:
Select
<column_name1>,
CASE
WHEN COND1 THEN RETURN EXPR1
WHEN COND2 THEN RETURN EXPR2
----------------
----------------
----------------
ELSE RETURN EXPR
END as <new_column_name>
from
<table_name>;
--Using case statement we can return values based on condition.
/* Case statements are 2 types
1 Simple Case
2 Searched Case */
==============
1 Simple Case
==============
-- Condition 1: Use Simple Case when conditions based on = operator.
/* Q:DISPLAY ENAME,DNAME from emp table based on below conditions:
If deptno =10 then display ACCOUNTS
=20 then display RESEARCH
=30 then display SALES
= others UNKNOWN */
select
ename, deptno,
case deptno
when 10 then 'ACCOUNTS'
when 20 then 'RESEARCH'
when 30 then 'SALES'
else 'unknown'
end dname
from emp
order by deptno;
-- Q:Increment employee's salaries as follows?
If JOB= CLERK then increment sal by 10%
SALESMAN then increment sal by 15%
MANAGER then increment sal by 20%
Others then increment sal by 5%
/*DDL-create alter drop, truncate, rename,flashback,purge
DML- insert, update,delete,insert all,merge */
Solution:
UPDATE emp
SET SAL =
CASE JOB
WHEN 'SALESMAN' THEN SAL+SAL *0.15
WHEN 'MANAGER' THEN SAL+SAL * 0.2
WHEN 'CLERK' THEN SAL+SAL*0.1
ELSE SAL+SAL* 0.05
END;
===============================
/* 2. Searched Case Statement
Use Searched Case Statement when conditions are not based on equality operator.
*/
===============================
Syntax:
select
<columns>,
CASE
WHEN <Cloumn_Name> <logical operator> THEN RETURN result1
WHEN <Cloumn_Name> <logical operator> THEN RETURN result2
-----------------
ELSE RETURN default value
END as <new_column>
from
<table_name>;
/* Q: Display ENAME,SAL,SALRANGE
Salary Range should
From 0-2000 Low Salary
From 2001-4000 Medium Salary
Above 4000 High Salary */
SELECT ENAME,SAL,
CASE
WHEN SAL BETWEEN 0 AND 2000 THEN 'Low Salary'
WHEN SAL BETWEEN 2001 AND 4000 THEN 'Medium Salary'
ELSE 'High Salary'
END AS SALRANGE
FROM EMP;
SELECT FIRST_NAME,SALARY,
CASE
WHEN SALARY BETWEEN 0 AND 10000 THEN 'Low Salary'
WHEN SALARY BETWEEN 10000 AND 20000 THEN 'Medium Salary'
ELSE 'High Salary'
END AS SALRANGE
FROM hr.employees
order by salary desc;
select *from hr.employees;
---------------------------------------
select ename,sal,case
when sal between 0 and 2000 then '0-2000'
when sal between 2001 and 4000 then '2001-4000'
else 'above 4000'
end as sal_rage
from emp
order by sal_rage desc;
-------------------------
Q:Display students SNO,TOTAL,AVG,RESULT?
CREATE TABLE student_results (
shno NUMBER PRIMARY KEY,
sname VARCHAR2(100),
telugu NUMBER(3),
hindi NUMBER(3),
english NUMBER(3),
maths NUMBER(3),
science NUMBER(3),
social NUMBER(3)
);
--------
INSERT ALL
INTO student_results (shno, sname, telugu, hindi, english, maths, science, social) VALUES (101, 'Arjun V.', 85, 78, 92, 95, 88, 84)
INTO student_results (shno, sname, telugu, hindi, english, maths, science, social) VALUES (102, 'Priya K.', 90, 82, 88, 76, 85, 91)
INTO student_results (shno, sname, telugu, hindi, english, maths, science, social) VALUES (103, 'Rohan S.', 72, 75, 80, 88, 79, 74)
INTO student_results (shno, sname, telugu, hindi, english, maths, science, social) VALUES (104, 'Ananya R.', 95, 91, 94, 99, 96, 98)
INTO student_results (shno, sname, telugu, hindi, english, maths, science, social) VALUES (105, 'Siddharth M.', 68, 70, 75, 82, 70, 72),
SELECT * FROM dual;
Commit;
INSERT ALL
INTO student_results (shno, sname, telugu, hindi, english, maths, science, social) VALUES (106, 'Kiran P.', 32, 45, 50, 28, 40, 42)
INTO student_results (shno, sname, telugu, hindi, english, maths, science, social) VALUES (107, 'Varun J.', 40, 30, 35, 45, 33, 50)
SELECT * FROM dual;
---------------------------------------
SELECT sname,
CASE
WHEN telugu >= 35 AND
hindi >= 35 AND
english >= 35 AND
maths >= 35 AND
science >= 35 AND
social >= 35
THEN 'Pass'
ELSE 'Fail'
END AS std_result
FROM student_results;
sname std_results
Arjun V. Pass
Priya K. Pass
Rohan S. Pass
Ananya R. Pass
Siddharth M. Pass
Kiran P. Fail
Varun J. Fail
==================
No comments:
Post a Comment