with절 트리구조 검색 조회 시 상위노드도 조회

부서테이블과 다른 테이블을 요리조리 조합해서 부서명 검색 시 상위에 있는 부서도 조회할 수 있도록 하는 쿼리를 구현해서 기록...

공통도 안 해주는 걸 내가 하다니 뿌듯

 

상황

• 부서테이블(dept로 칭함)이 있고 부서가 속하는 다른 테이블(억지로 예를 들자면 1계층만 있는 메뉴가 있는데 메뉴를 사용하는 부서를 지정, 단 부서는 1개의 메뉴에만 속하고 메뉴는 다수의 부서를 가질 수 있는 관계, menu로 칭함)이 있음.
• dept테이블의 컬럼으로는 cd(부서코드), cdnm(부서명), hcd(상위코드), hstcd(최상위코드), rnk(서열, 같은 노드 내 서열), lvl(차수, 부서레벨), useyn(사용여부)
• menu테이블의 컬럼으로는 sn(메뉴일련번호), nm(메뉴명), deptcd(부서코드)
• 부서명을 검색하면 검색한 부서의 상위 부서가 있으면 상위 부서까지 조회되도록 하는 것이 목표
• 체크박스가 있는 트리구조 컴포넌트에 뿌려준다고 가정
• 특정 메뉴를 클릭해서 모든 부서를 조회하는데 속해있는 부서라면 chk를 1로, 없다면 0

 

쿼리

WITH target_dept AS (
                    SELECT
                           B.cd
                         , B.hcd
                      FROM dept B
                     WHERE B.useyn = 'Y'
                       AND B.cdnm LIKE '%' || '[검색하려는 부서명]' || '%'
                    START WITH B.hstcd = '[최상위 부서코드]'
                    CONNECT BY PRIOR B.cd = B.hcd
                    )
SELECT
       B.cd
     , B.nm
     , B.lvl
     , CASE WHEN EXISTS (
                         SELECT
                                1
                           FROM menu A
                          WHERE B.cd = A.deptcd
                            AND A.sn = '[특정 메뉴 일련번호]'
                        ) THEN '1'
                          ELSE '0' END AS chk
  FROM dept B
     , target_dept C
 WHERE B.cd = C.cd(+)
START WITH B.cd IN (
                    SELECT
                           hcd
                      FROM target_dept
                   )
CONNECT BY PRIOR B.cd = C.hcd
ORDER SIBLINGS BY B.lvl, B.rnk

 

with절로 부서명으로 검색한 결과를 미리 담고 담은 target_dept를 dept와 join 하면서 target_dept에 상위 부서코드도 조회하는 쿼리를 완성했다.

아직 start with~connect by의 완벽한 이해가 부족하므로 추후 글을 남겨야겠다.