-
Notifications
You must be signed in to change notification settings - Fork 0
/
06. Conditional Expression.sql
80 lines (63 loc) · 2.28 KB
/
06. Conditional Expression.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
/*
Author : Md Moniruzzaman Monir
*/
-- use of CASE expression and DECODE() function
-- The CASE expression complies with the ANSI SQL. The DECODE function is specific to Oracle syntax.
/*
CASE EXPRESSION
======================
CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END
Oracle server searches for the first WHEN ... THEN pair for which expr is equal to comparison_expr and returns return_expr.
If none of the WHEN ... THEN pairs meet this condition, and if an ELSE clause exists, then the Oracle server returns
else_expr.
Otherwise, the Oracle server returns a null. You cannot specify the literal NULL for all the return_exprs and the else_expr.
All of the expressions ( expr, comparison_expr, and return_expr) must be of the same data type, which can be CHAR, VARCHAR2,
NCHAR, or NVARCHAR2.
*/
/*
Problem Statement :
===================
If JOB_ID is IT_PROG, the salary increase is 10%;
If JOB_ID is ST_CLERK, the salary increase is 15%;
If JOB_ID is SA_REP, the salary increase is 20%.
For all other job roles, there is no increase in salary.
*/
-- Using CASE expression
SELECT
LAST_NAME,
JOB_ID,
SALARY,
(CASE JOB_ID
WHEN 'IT_PROG' THEN 1.10*SALARY
WHEN 'ST_CLERK' THEN 1.15*SALARY
WHEN 'SA_REP' THEN 1.20*SALARY
ELSE SALARY
END) "REVISED_SALARY",
(CASE
WHEN SALARY<5000 THEN 'Low'
WHEN SALARY<10000 THEN 'Medium'
WHEN SALARY<20000 THEN 'Good'
ELSE 'Excellent'
END) QUALIFIED_SALARY
FROM HR.EMPLOYEES;
-- using DECODE Function
SELECT
LAST_NAME,
JOB_ID,
SALARY,
DECODE(JOB_ID,
'IT_PROG', 1.10*SALARY,
'ST_CLERK', 1.15*SALARY,
'SA_REP', 1.20*SALARY,
SALARY) REVISED_SALARY,
DECODE(TRUNC(SALARY/5000, 0),
0, 'Low',
1, 'Medium',
2, 'Good',
3, 'Good',
'Excellent') QUALIFIED_SALARY
FROM HR.EMPLOYEES;