데이터베이스 질문과 답변
급여관련 쿼리 중 decode함수 질문입니다.
0
2
1,020
by 김주헌 [2016.08.30 15:12:18]
웹 콘솔로 출력된 쿼리입니다. 앞서 122번 째 줄부터 DECODE함수를 써서 재직,퇴직,휴직일 때의 한달 간 실제 근무한 일수(real_day1)를 구한 쿼리가 잘 못 된거 같습니다. 실행 시켜보니 112번 째 줄의 f.total_day가 잘못 되었다고 오류가 뜨는데요,
null as real_day1 이렇게 하면 실행이 되긴합니다.
저 부분에 뭐가 잘못 된 건가요?
앞뒤없이 쿼리만 내놓고 질문올려서 죄송합니다
WITH w_final AS
(SELECT sum_ym ,
to_char(add_months(to_date(sum_ym,'YYYYMM'),-1), 'YYYYMM') sum_pym ,
substr(sum_ym, 1,4) sum_year ,
substr(sum_ym, 5,2) sum_month ,
to_char(add_months(to_date(sum_ym,'YYYYMM'),-12), 'YYYY') sum_pyear ,
day_month ,
day_month - holiday_cnt(sum_ym||'01',1, sum_ym||day_month,1,1) AS total_day ,
pay_emer ,
pay_comm ,
pay_intern ,
hpay1 ,
hpay2 ,
pay_unemins ,
'' search_group ,
'' search_no ,
'' search_nm ,
'' search_state
FROM
(SELECT '2016'||'07' sum_ym ,
to_char(last_day(to_date('2016'||'07','YYYYMM')),'DD') day_month
FROM dual
) ,
(SELECT pay_comm,
pay_emer,
pay_intern
FROM
(SELECT code,
trim(cd_desc) cd_desc
FROM tb_cccddatas
WHERE cd_classfier = 'C1040'
) pivot ( max(cd_desc) FOR code IN ('PY001' AS pay_comm ,'PY002' AS pay_emer ,'PY003' AS pay_intern))
) ,
(SELECT hpay1,
hpay2
FROM
(SELECT code,
trim(cd_desc) cd_desc
FROM tb_cccddatas
WHERE cd_classfier = 'C1041'
) pivot ( max(cd_desc) FOR code IN ('PT001' AS hpay1 ,'PT002' AS hpay2))
) ,
(SELECT pay_unemins
FROM
(SELECT code,
trim(cd_desc) cd_desc
FROM tb_cccddatas
WHERE cd_classfier = 'C1042'
) pivot ( max(cd_desc) FOR code IN ('PE001' AS pay_unemins))
)
) ,
w_nhis AS
(SELECT emp_no reg_no ,
nm emp_nm ,
trim(insurance) insurance
FROM vi_upload_nh,
w_final f
WHERE ins_date LIKE f.sum_ym||'%'
) ,
w_nps AS
(SELECT substr(pers_contrib, 1,6) reg_no ,
nm emp_nm ,
base_income ,
insur_mon ,
trim(empler_contrib) empler_contrib ,
substr(pers_contrib, 1,6) pers_contrib
FROM vi_upload_np,
w_final f
WHERE ins_date LIKE f.sum_ym||'%'
) ,
w_nt AS
(SELECT 'Y' data_yn,
pay_mon_lb,
pay_mon_ub ,
size_fam_deduct01,
size_fam_deduct02,
size_fam_deduct03,
size_fam_deduct04,
size_fam_deduct05,
size_fam_deduct06,
size_fam_deduct07,
size_fam_deduct08,
size_fam_deduct09,
size_fam_deduct10,
size_fam_deduct11
FROM vi_upload_nt,
w_final f
WHERE ins_date LIKE f.sum_pyear||'%'
) ,
w_incpay AS
(SELECT ie.emp_no ,
ie.grade ,
trim(ib.base) inc_base
FROM vi_upload_ie ie
INNER JOIN w_final f
ON 1=1
INNER JOIN vi_upload_ib ib
ON ib.ins_date LIKE f.sum_ym||'%'
AND ib.grade=ie.grade
WHERE ie.ins_date LIKE f.sum_ym||'%'
) ,
w_agtpay AS
(SELECT emp_no,
min(confirm_yn) agt_confirm_yn
FROM tb_cmagtpay,
w_final f
WHERE pay_ym = f.sum_ym
GROUP BY emp_no
)
, w_emp_info as (
select
min(f.total_day) total_day,
nvl(sum(d.cut_day),0) cut_day,
nvl(sum(d.baby_break),0) baby_break,
nvl(sum(d.bear_vac),0) bear_vac,
min(f.total_day)-nvl(sum(d.cut_day),0)-nvl(sum(d.baby_break),0)-nvl(sum(d.bear_vac),0)
+ (SELECT sum(mens)
FROM tb_cmagtduty
WHERE substr(duty_ymd,1,6) = '2016'||'07'
AND emp_no = a.emp_no) real_day_pay_house, -- 가계지원비 계산 시 보건휴가 제외
/***************************************************
DECODE(
MIN(a.wk_status_cd),
'10',
case
when NVL(SUM(d.bear_vac),0) <> 0 and MIN(substr(f.sum_ym, 5,2)) - MIN(SUBSTR(d.duty_ymd, 5,2)) = '0' -- 첫째달이면
then min(f.total_day) - 0
when NVL(SUM(d.bear_vac),0) <> 0 and MIN(substr(f.sum_ym, 5,2)) - MIN(substr(d.duty_ymd, 5,2)) = '1' -- 둘째달이면
then min(f.total_day) - 0
when NVL(SUM(d.bear_vac),0) <> 0 and MIN(substr(f.sum_ym, 5,2)) - MIN(substr(d.duty_ymd, 5,2)) = '2' -- 세째달이면
then min(f.total_day) - ( (MIN(day_month) - MIN(substr(d.duty_ymd, 7,2))) - holiday_cnt(MIN(f.sum_ym)||MIN(substr(d.duty_ymd, 7,2)),1, MIN(f.sum_ym)||MIN(day_month),1,1) ) -- 휴가시작일부터 이달 마지막날까지 일요일계산해서 뺀다.
when NVL(SUM(d.bear_vac),0) <> 0 and MIN(substr(f.sum_ym, 5,2)) - MIN(substr(d.duty_ymd, 5,2)) = '3' -- 네째달이면
then min(f.total_day) - (MIN(substr(d.duty_ymd, 7,2))) - holiday_cnt(MIN(f.sum_ym)||'01',1, MIN(f.sum_ym)||substr(MIN(d.duty_ymd), 7,2),1,1)
else min(f.total_day)-nvl(sum(d.cut_day),0)-nvl(sum(d.baby_break),0)-nvl(sum(d.bear_vac),0)
end
,
'20',
case
when NVL(SUM(d.baby_break),0) <> 0 and MIN(substr(f.sum_ym, 0,6)) - MIN(SUBSTR(d.duty_ymd, 0,6)) = '0'
then min(f.total_day) - (MIN(substr(d.duty_ymd, 7,2))) - holiday_cnt(MIN(f.sum_ym)||'01',1, MIN(f.sum_ym)||substr(MIN(d.duty_ymd), 7,2),1,1)
when NVL(SUM(d.baby_break),0) <> 0 and MIN(substr(f.sum_ym, 0,6)) - MIN(SUBSTR(ADD_MONTHS(TO_DATE(d.duty_ymd,'YYYYMMDD'), 12), 0,6)) = '0'
then min(f.total_day) - ( (MIN(day_month) - MIN(substr(d.duty_ymd, 7,2))) - holiday_cnt(MIN(f.sum_ym)||MIN(substr(d.duty_ymd, 7,2)),1, MIN(f.sum_ym)||MIN(day_month),1,1) )
else NULL
end
,
'30',
case
when min(a.wk_end_ymd) = min(f.sum_ym||min(SUBSTR(a.wk_end_ymd, 7,2)))
then min(f.total_day) - (MIN(substr(a.wk_end_ymd, 7,2))) - holiday_cnt(MIN(f.sum_ym)||'01',1, MIN(f.sum_ym)||substr(MIN(a.wk_end_ymd)-1, 7,2),1,1)
else NULL
END
,
null
) real_day1,
************************************************************8*/
min(f.sum_ym) pay_ym,
a.emp_no,
min(a.emp_nm) emp_nm,
min(a.emp_no) reg_no,
min(d.confirm_yn) confirm_yn,
min(b.acd_group) acd_group,
trim(min(b.acd_nm)) acd_nm,
min(a.rule_cd) rule_cd,
min(a.work_emp_type) work_emp_type, -- 고용형태
min(a.wk_status_cd) wk_status_cd, -- 상담원 상태 30 휴직, 20퇴사
min(a.org_yn) org_yn,
min(trim(a.wk_start_ymd)) wk_start_ymd,
min(a.wk_end_ymd) wk_end_ymd,
min(f.day_month) day_month,
nvl(sum(d.absent),0) absent,
nvl(min(inc.inc_base),0) pay_class,
nvl2(min(inc.inc_base),'N','Y') incpay_err,
min(f.total_day) - case when min(trim(a.wk_start_ymd)) is not null and ( to_number(min(trim(a.wk_start_ymd))) - to_number(min(f.sum_ym)||'01') ) > 0
then holiday_cnt(min(f.sum_ym)||'01',1, min(a.wk_start_ymd),0, 1)
else 0 end
- case when min(trim(a.wk_end_ymd)) is not null and ( to_number(min(f.sum_ym)||min(f.day_month)) - to_number(min(trim(a.wk_end_ymd))) +1 ) > 0
then holiday_cnt(min(a.wk_end_ymd),1, min(f.sum_ym)||min(f.day_month),1, 1)
else 0 end
- nvl(sum(d.absent),0)
as day_work,
nvl(sum(late),0)
+ nvl(sum(outting),0)
+ nvl(sum(early),0)
as hpay_minus,
case when min(trim(a.wk_start_ymd)) is null then 0 else floor((min(f.sum_ym)||'31' - min(a.wk_start_ymd)) / 10000) end work_year,
case when min(a.rule_cd) = '62' or min(a.rule_cd) = '10' then 30000 else 0 end pay_assadm,
case when '0' ='1' then 400000 else 0 end pay_tradhol
from tb_caagent a
inner join tb_caacdagt c on a.emp_no = trim(c.emp_no)
inner join tb_ccacdgroup b on b.acd_group = c.acd_group
inner join w_final f on 1=1
left join tb_cmagtduty d on (d.duty_ymd between f.sum_ym||'01' and f.sum_ym||'31' and a.emp_no = d.emp_no and d.confirm_yn = 'Y')
left join w_incpay inc on inc.emp_no = a.emp_no
where
a.rule_cd in('10','20','70','80','60','55')
and a.wk_status_cd in ('10', '20', '30')
group by a.emp_no )
, w_emp_pay
as
(SELECT a.emp_no,
a.emp_nm,
a.reg_no,
a.acd_group,
a.acd_nm,
a.cut_day,
a.baby_break,
a.bear_vac,
a.real_day1, a.pay_ym, a.confirm_yn, a.rule_cd,
a.work_emp_type,
a.wk_status_cd,
a.org_yn,
a.wk_start_ymd,
a.wk_end_ymd,
a.day_month,
a.absent,
a.pay_class,
a.incpay_err,
a.day_work,
a.hpay_minus,
a.work_year,
a.pay_assadm,
a.pay_tradhol,
CASE
WHEN a.work_emp_type = '04'
THEN '0'
WHEN a.acd_group = '1400'
THEN f.hpay2
ELSE f.hpay1
END hpay, --시용직 기본급(2016.03. 신규 요구사항)
case when a.work_emp_type = '04' then floor(f.pay_intern
*a.real_day1 /f.total_day /10
)*10
when a.acd_group = '1400' -- 퇴사 '20' 추가
then case when a.wk_status_cd
= '30' then
0
else
floor(f.pay_emer *a.real_day1 /f.total_day /10)*10
end
else
case
when a.wk_status_cd = '30' then
0
else
floor(f.pay_comm *a.real_day1 /f.total_day /10)*10
end
end pay_basic, --휴직자 기본급 계산(휴직자 일 경우 가계지원비 차감)
case
when a.work_emp_type = '04' then
floor(50000 *a.real_day1 /f.total_day /10)*10
else
floor(100000 *a.real_day1 /f.total_day /10)*10
end pay_lunch, --중식비(시용직일 경우 절반만 줌)
case
when a.work_emp_type = '04' then
floor(50000 *a.real_day1 /f.total_day /10)*10
else
floor(100000 *a.real_day1 /f.total_day /10)*10
end pay_trans, --교통비(시용직일 경우 절반만 줌)
case when
a.work_emp_type = '01' then
floor(100000 * a.real_day_pay_house /f.total_day /10)*10
else
0
end pay_house, --가계지원비(01 무기계약직; 2016.03. 신규 요구사항)
case when a.work_year = 0 then 0 else (a.work_year-1)
* 10000 + decode(a.work_year,0,0,30000)
end pay_senior,
case a.rule_cd
when '70'
/*팀장*/
then
case
when f.total_day = a.day_work then
300000
else
floor(300000 *a.day_work /f.day_month /10)*10
end
when '80'
/*부팀장*/
then
case
when f.total_day = a.day_work then
200000
else
floor(200000 *a.day_work /f.day_month /10)*10
end
when '20'
/*파트장*/
then
case
when f.total_day = a.day_work then
100000
else
floor(100000 *a.day_work /f.day_month /10)*10
end
else
0
end pay_long,
case
when a.rule_cd = '11' or a.rule_cd = '61' then
floor(50000 *a.day_work /f.day_month /10)*10
else
0
end pay_right -- 야간
from w_emp_info a, w_final f ) , w_pay_extra as ( select a.emp_no,
b.acd_group, min(a.org_yn) org_yn, min(a.rule_cd) rule_cd, min(a.wk_status_cd) wk_status_cd, min(e.hpay) hpay, sum(d.extwk) extwk, sum(d.nightwk) nightwk, round(
case
when min(f.sum_month) = '01' then
0
else
nvl(sum(d.extwk),0)*1.5*min(e.hpay)
end) pay_wk, round(
case
when min(f.sum_month) = '01' then
0
else
nvl(sum(d.nightwk),0)* 0.5 *min(e.hpay)
end) pay_ot from tb_caagent a inner join tb_caacdagt c on a.emp_no = trim(c.emp_no) inner join tb_ccacdgroup b on b.acd_group = c.acd_group inner join w_emp_pay e on e.emp_no=a.emp_no inner join w_final f on 1=1 left join tb_cmagtduty d on ( ( f.sum_month = '12' and d.duty_ymd between f.sum_pym||'01' and f.sum_ym||'31' or f.sum_month = '01' and d.duty_ymd like f.sum_pym||'%' or f.sum_month != '12' and f.sum_month != '01' and d.duty_ymd like f.sum_pym||'%' ) and a.emp_no = d.emp_no and d.confirm_yn = 'Y')
where a.rule_cd in('10', '20','70','80','60','55')
and a.wk_status_cd
in ('10', '20', '30')-- AND (a.wk_end_ymd IS NULL OR a.wk_end_ymd = f.sum_ym||'%')
group by a.emp_no, b.acd_group )
, w_pay_info
as
(SELECT z.emp_no,
z.emp_nm,
z.reg_no,
z.acd_group,
z.acd_nm,
w.pay_wk,
w.pay_ot,
w.extwk,
w.nightwk,
z.pay_assadm pay_misc,
(z.pay_basic+z.pay_lunch+z.pay_trans+ w.pay_wk+w.pay_ot +z.pay_class+z.pay_long+z.pay_senior+z.pay_assadm+z.pay_house) pay_amt,
z.pay_basic,
z.pay_lunch,
z.pay_trans,
z.pay_class,
z.incpay_err,
z.pay_senior,
z.pay_long,
z.cut_day,
z.baby_break,
z.bear_vac, z.real_day1, z.pay_ym, z.confirm_yn, z.rule_cd, z.work_emp_type,
z.wk_status_cd,
z.org_yn,
z.wk_start_ymd,
z.wk_end_ymd,
z.day_month,
z.absent,
z.day_work,
z.hpay_minus,
z.work_year,
z.pay_assadm,
z.pay_tradhol,
z.hpay,
z.pay_house,
z.pay_right
FROM w_emp_pay z,
w_pay_extra w
WHERE z.emp_no = w.emp_no
AND z.acd_group = w.acd_group
) , w_deduct
as
(SELECT f.sum_ym,
q.emp_no,
q.reg_no,
nvl2
(
(SELECT data_yn
FROM w_nt
WHERE pay_mon_lb * 1000 <=(q.pay_amt-q.pay_lunch)
AND pay_mon_ub * 1000 >(q.pay_amt-q.pay_lunch)
),'N','Y'
)
nt_err,
nvl
(
(SELECT size_fam_deduct01
FROM w_nt
WHERE pay_mon_lb * 1000 <=(q.pay_amt-q.pay_lunch)
AND pay_mon_ub * 1000 >(q.pay_amt-q.pay_lunch)
),0
)
tax_income,
floor(nvl
(
(SELECT size_fam_deduct01
FROM w_nt
WHERE pay_mon_lb * 1000 <=(q.pay_amt-q.pay_lunch)
AND pay_mon_ub * 1000 >(q.pay_amt-q.pay_lunch)
),0
)
/10) tax_jumin,
nvl(w_nps.empler_contrib,0) tax_pension,
nvl2(w_nps.empler_contrib,'N','Y') nps_err,
nvl(w_nhis.insurance, 0) tax_insu,
nvl2(w_nhis.insurance,'N','Y') nhis_err,
floor(NVL(q.pay_amt*f.pay_unemins,0)/10)*10 tax_emp,
q.hpay*q.hpay_minus tax_misc,
q.confirm_yn,
agt_confirm_yn
FROM w_pay_info q
INNER JOIN w_final f
ON 1=1
LEFT JOIN w_nhis
ON w_nhis.reg_no = q.reg_no
LEFT JOIN w_nps
ON w_nps.reg_no = q.reg_no
LEFT JOIN w_agtpay ap
ON ap.emp_no=q.emp_no
) , TBL
as
(SELECT r.emp_nm,
CASE
WHEN
(
trim(d.agt_confirm_yn) IS NOT NULL
AND trim(d.agt_confirm_yn) ='Y'
)
THEN '확정. '
END ||
CASE
WHEN
(
trim(r.confirm_yn) IS NULL
OR trim(r.confirm_yn) !='Y'
)
THEN '근무정보 미확정. '
END ||
CASE
WHEN d.nhis_err='Y'
THEN '건강보험 EXCEL 항목 누락. '
END ||
CASE
WHEN d.nps_err='Y'
THEN '국민연금 EXCEL 항목 누락. '
END ||
CASE
WHEN d.nt_err='Y'
THEN '간이세액표 EXCEL 항목 범위 누락. '
END ||
CASE
WHEN r.incpay_err='Y'
THEN '성과급 EXCEL 항목 누락. '
END AS prc_stat,
r.acd_nm,
r.emp_no,
r.wk_start_ymd,
r.pay_basic,
r.pay_lunch,
r.pay_trans,
r.pay_wk,
r.pay_ot,
r.pay_class,
r.pay_senior,
r.pay_long,
r.pay_house,
r.pay_right,
r.pay_misc,
r.pay_tradhol,
r.pay_amt,
d.tax_pension,
d.tax_insu,
d.tax_emp,
d.tax_income,
d.tax_jumin,
d.tax_misc,
(d.tax_income+d.tax_jumin+d.tax_pension+d.tax_insu+d.tax_emp+d.tax_misc) tax_amt,
nvl((r.pay_amt-(d.tax_income+d.tax_jumin+d.tax_pension+d.tax_insu+d.tax_emp+d.tax_misc)),0) sal,
f.sum_ym,
f.sum_pym,
f.sum_year,
f.sum_month,
f.sum_pyear,
f.day_month,
f.total_day,
f.pay_emer,
f.pay_comm,
f.pay_intern,
f.hpay1,
f.hpay2,
f.pay_unemins,
f.search_no,
f.search_nm,
f.search_state,
r.reg_no,
r.acd_group,
d.nhis_err,
d.nps_err,
d.nt_err,
r.incpay_err,
r.confirm_yn,
d.agt_confirm_yn,
r.cut_day,
r.baby_break,
r.bear_vac, r.real_day1, r.rule_cd, r.work_emp_type,
r.wk_status_cd,
r.org_yn,
r.wk_end_ymd,
r.extwk,
r.nightwk,
r.absent,
r.day_work,
r.hpay_minus,
r.work_year,
r.pay_assadm,
r.hpay
FROM w_pay_info r
INNER JOIN w_final f
ON 1=1
LEFT JOIN w_deduct d
ON d.emp_no=r.emp_no
)
SELECT *
FROM
(SELECT LIST.*,
ceil(ROWNUM/'100000') AS pageNum,
count(ROWNUM) over() - ROWNUM + 1 rnum,
CNT.recordNum
FROM
(SELECT count(*) AS recordNum
FROM TBL
) CNT,
(SELECT *
FROM TBL
ORDER BY acd_group,
emp_nm
) LIST
)
WHERE pageNum='1'
by atumlee
[2016.08.30 16:48:30]
주석 처리한 부분만 봤는데, 141번째 라인에 문제가 있어 보입니다.
--* 141라인 원본 소스 when NVL(SUM(d.baby_break),0) <> 0 and MIN(substr(f.sum_ym, 0,6)) - MIN(SUBSTR(ADD_MONTHS(TO_DATE(d.duty_ymd,'YYYYMMDD'), 12), 0,6)) = '0' --* 141라인 수정 소스 when NVL(SUM(d.baby_break),0) <> 0 and MIN(substr(f.sum_ym, 0,6)) - MIN(TO_CHAR(ADD_MONTHS(TO_DATE(d.duty_ymd,'YYYYMMDD'),12),'YYYYMM')) = '0' ...
by 김주헌
[2016.08.31 15:51:05]
답 주셔서 감사합니다.
수정 해주신 부분도 문제가 있었지만 여전히 112번 째 줄의 GROUP BY문제가 계속 생깁니다.
ㅠㅡㅠ
) 버튼을 클릭하여 작성 하시면 됩니다.