[ORACLE] DECODE,NVL 기타함수

ECODE,NVL 기타함수에 대해서 알아볼 것 이다.

---

기타함수 

•  DECODE(expr, 조건, 참_값, 거짓_값) <= 3항 연산자( if(a>b ? 5 : 3) ) 
•  NVL(None Value Logic) =>nvl(expr1, expr2) : expr1이 NULL일 때 expr2 값을 할당한다.
• CASE WHEN ~ THEN

---

●예제

 

기타함수
DECODE(expr, 조건, 참_값, 거짓_값)
● select distinct job_id, decode(job_id, 'SA_MAN', 'Sales_dept', 'SA_REP', 'Sales Dept', 'Others') "DEPTs" from employees; 

select distinct job_id, 
decode(job_id, 'SA_MAN', 'Sales_dept', 'SA_REP', 'Sales Dept', 'Others') "DEPTs" 
from employees;

●CASE WHEN ~ THEN
●select distinct job_id,
case when job_id='SA_MAN' then 'Sales_Dept'
when job_id='SA_REP' then 'Sales_Dept'
else 'Others'
end as DEPTS
 from employees;
//where job_id Like 'SA%'; 

select distinct job_id,
case when job_id='SA_MAN' then 'Sales_Dept'
when job_id='SA_REP' then 'Sales_Dept'
else 'Others'
end as DEPTS
 from employees;
//where job_id Like 'SA%';

● SA_REP와 SA_MAN에서 급료가 10000이상이면 high_sal, 7000~10000이면 mid_sal, 그외는  low_sal로 표시되도록 cas when then 문으로 만들어 보시오.

select distinct job_id, salary,
case when salary >='10000' then 'high_sal'
when salary >= '7000' then 'mid_sal'
else 'low_sal'
end as SAL_Hi_MiD_Low
 from employees
where job_id like 'SA%';

select salary, job_id,
case when salary >= 10000 then 'hight_sal'
      when salary >= 7000 then 'mid_sal'
/* when (salary between 7000 and 10000) then 'mid_sal' */
else 'low_sal'
end as "sal_grade"
from employees
where job_id like 'SA%'; /* where job_id in ('SA_REP', 'SA_MAN') */
order by sal_grade desc; */

●SA_REP와 SA_MAN 에서 급료가 10000이상이면 보너스 10%, 7000~10000이면 보너스 7% 기타는 5%로 주는 case - when - then 문으로 만들어서 last_name, job_id, salary, 보너스가 포함된 sal_bo 항목으로 보이시오. 

select  job_id, last_name, salary,
case when salary >='10000' then salary*1.1 // salary = salary + salary*0.1
     when (salary between 7000 and 10000) then salary*1.7 //salary = salary + salary*0.7
      else salary*1.5
end as sla_bo
 from employees
where job_id like 'SA%'
order by sla_bo desc;

 

● select job_id, last_name, salary, CASE WHEN salary >= '10000' THEN salary * 1.1 WHEN salary >= '7000' THEN salary * 1.07 ELSE salary * 1.05 END as "sal_bo", CASE WHEN salary >= '10000' THEN 'High_sal' WHEN salary >= '7000' THEN 'Mid_sal' ELSE 'Low_sal' END as "Sal_Level" from employees where job_id in ('SA_MAN', 'SA_REP') order by salary DESC;

select job_id, last_name, salary, 
CASE WHEN salary >= '10000' THEN salary * 1.1 
WHEN salary >= '7000' THEN salary * 1.07 ELSE salary * 1.05
END as "sal_bo", 
CASE WHEN salary >= '10000' 
THEN 'High_sal' WHEN salary >= '7000' 
THEN 'Mid_sal' ELSE 'Low_sal' END as "Sal_Level" 
from employees where job_id in ('SA_MAN', 'SA_REP') order by salary DESC;

●NVL(None Value Logic)

 

●IT_PROG 부서에서 (job_id)에서 commission_pct가 null인 직원의 commission_pct를 0으로 만드시오.
=> 빅데이터 분석에서 없는 값이 있거나, null로 되어 있으면 이런 데이터를 대상으로 어느 연산을 수행하면 무조건 값이 없음으로 나오기 때문에 이런null(0은 아니지만 값을 지정하지 않은 것)이 있으면 전체 데이터가 무의미해진다.

select distinct job_id, salary, NVL(commission_pct, 0) "COMMPCT_with_no_commPCT" from employees
where job_id = 'IT_PROG';