SELECT A.DEPT_CODE as oflcd,
B.DEPT_NAME as oflvNm,
sum(empTot) empTot,
MAX(DECODE ( school_car_code , 'T' , EMP_NAME )) as dgrdr,
MAX(DECODE ( school_car_code , 'Q' , EMP_NAME )) as dgrma,
MAX(DECODE ( school_car_code , 'M' , EMP_NAME )) as univGrdt,
MAX(DECODE ( school_car_code , 'L' , EMP_NAME )) as colgeGrdt,
MAX(DECODE ( school_car_code , 'H' , EMP_NAME )) as hischGrdt,
MAX(DECODE ( school_car_code , 'Z' , EMP_NAME , '' , EMP_NAME)) as othr
FROM
(
SELECT DEPT_CODE ,
school_car_codE ,
SUBSTR(MAX(SYS_CONNECT_BY_PATH(EMP_NAME, ',')), 2) AS EMP_NAME ,
sum(1) as empTot
FROM
(select A.EMP_NO,
EMP_NAME ,
school_car_code,
A.DEPT_CODE,
ROW_NUMBER() OVER(PARTITION BY A.DEPT_CODE,school_car_codE ORDER BY A. DEPT_CODE,school_car_codE) AS RNUM
from hr_orde_master A,
hr_pers_master B
where A.EMP_NO =B.EMP_NO
AND (A.EMP_no, apply_order_date, seq) in
(select emp_no,
substr(dateseq,1,8),
to_number(substr( dateseq,9,1))
from
(select emp_no,
max(apply_order_date
||to_char(seq)) dateseq
from hr_orde_master
where emp_no in
(select emp_no
from hr_pers_master a
where a.company_code like '100'
and a.join_date <= to_char((LAST_DAY( TO_DATE(substr(20161117,1,6)
||'01'))),'YYYYMMDD')
and (a.retire_date >= to_char((LAST_DAY( TO_DATE(substr(20161117,1,6)
||'01'))),'YYYYMMDD')
or a.retire_date is null )
)
and apply_order_date <= to_char((LAST_DAY(TO_DATE( substr(20161117,1,6)
||'01'))),'YYYYMMDD')
and confirm_tag = 'Y'
group by emp_no
)
)
)
START WITH RNUM = 1
CONNECT BY PRIOR RNUM = RNUM - 1
AND PRIOR DEPT_CODE = DEPT_CODE
AND PRIOR school_car_code=school_car_code
GROUP BY DEPT_CODE ,
school_car_code
order by DEPT_CODE ,school_car_code
) A,
VW_HR_CODE_DEPT B
WHERE A.DEPT_CODE=B.DEPT_CODE
GROUP BY A.DEPT_CODE ,
B.DEPT_NAME ,
ROW_NUM
ORDER BY ROW_NUM
위와같은 쿼리인데요... 가장밖의 A와 B를 조인하면 A쿼리만 실행할때와는 다른 데이터들이 나오더라구요.
아마도 조인하면서 order by 가 풀리던가? 뭐 꼬이는거 같은데 이럴때는 무슨 조건을 주어야 할런지요? 궁금합니다.
|