안녕하세요 구루비에서 많은 도움을 받고 있습니다.
with relation(bscode, upbscode, bsname) as
(
select 'aaaaa' ,'aaaa' ,'안전팀' from dual union all
select 'aaaa' ,'aaa' ,'안전팀처' from dual union all
select 'aaa' ,'aa' , '시설부' from dual union all
select 'aa' ,'a' , '시설처' from dual union all
select 'bbbbb' ,'bbbb' ,'위험팀' from dual union all
select 'bbbb' ,'bbb' ,'위험팀처' from dual union all
select 'bbb' ,'bb' , '자재부' from dual union all
select 'bb' ,'a' , '자재처' from dual union all
select 'a', null ,'본사' from dual
) , insa (name, bscode, amt) as
(
select 'Kevin', 'aaa', '1000' from dual union all
select 'Michael', 'bbbbb', '2000' from dual union all
select 'Uncle', 'aaaaa', '4000' from dual
)
부모 자식 관계의 부서 데이터가 있고
인사정보에 부서코드가 들어있습니다.
출력할때 해당 부서부터 상위부서 2개를 더 찾아서 보여주고자 합니다.
이름 차상위부서 상위부서 부서 부서코드 금액
kevin 본사 시설처 시설부 aaa 1000
Michael 자재처 자재부 위험팀처 bbbb 2000
Uncle 시설부 안전팀처 안전팀 aaaaa 40000
이런식으로..
select a.name , regexp_substr(b.nm,'[^,]+',1,3) updept1nm , regexp_substr(b.nm,'[^,]+',1,2) updept2nm , regexp_substr(b.nm,'[^,]+',1,1) deptnm , a.bscode deptcd , a.amt from insa a, ( select connect_by_root(bscode) code, sys_connect_by_path(bsname,',') nm from relation a where connect_by_isleaf = 1 connect by prior upbscode = bscode and level <= 3 ) b where a.bscode = b.code order by 1