Case and Decode in SQL

SQL provide two conditional expression.
1.CASE
2.DECODE
It works  same as If-else Statement.

CASE Expression

Facilitates conditional inquiries by doing the work of IF-ELSE statement.

Syntax:


CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END

Query: implement query to increase salary of emplooyees with below citeria

a) If the job is IT_PROG increment is 10%.
b) If the job is ST_CLERK increment is 15%.
b) If the job is SA_REP increment is 20%.

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"
FROM employees;

case

DECODE Function

Facilitates conditional inquiries by doing the work of a CASE expression or an IF-THEN-ELSE statement:

DECODE (col|expression, search1, result1
[, search2, result2,...,]
[, default])

Query: implement query to increase salary of emplooyees with below citeria

a) If the job is IT_PROG increment is 10%.
b) If the job is ST_CLERK increment is 15%.
b) If the job is SA_REP increment is 20%.


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
FROM employees;

case

Leave a Reply

Your email address will not be published. Required fields are marked *