안녕하세요.
계층형 쿼리를 작성 도중 구현하고 싶은 점이 있지만, 여러 방법을 해도 생각한 대로 구현되지 않아 질문 올립니다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | WITH T AS ( SELECT '학교' CODE_NM, 'SCHOOL1' CODE, NULL PARENT_CODE FROM DUAL UNION ALL SELECT '서울학교' CODE_NM, 'SCHOOL3' CODE, 'SCHOOL1' PARENT_CODE FROM DUAL UNION ALL SELECT '서울초등학교' CODE_NM, 'SCHOOL4' CODE, 'SCHOOL3' PARENT_CODE FROM DUAL UNION ALL SELECT '서울초등학교-1' CODE_NM, 'SCHOOL5' CODE, 'SCHOOL4' PARENT_CODE FROM DUAL UNION ALL SELECT '서울초등학교-2' CODE_NM, 'SCHOOL6' CODE, 'SCHOOL4' PARENT_CODE FROM DUAL UNION ALL SELECT '서울초등학교-3' CODE_NM, 'SCHOOL7' CODE, 'SCHOOL4' PARENT_CODE FROM DUAL UNION ALL SELECT '서울초등학교-4' CODE_NM, 'SCHOOL8' CODE, 'SCHOOL4' PARENT_CODE FROM DUAL UNION ALL SELECT '서울초등학교-5' CODE_NM, 'SCHOOL9' CODE, 'SCHOOL4' PARENT_CODE FROM DUAL UNION ALL SELECT '학교' CODE_NM, 'SCHOOL2' CODE, NULL PARENT_CODE FROM DUAL UNION ALL SELECT '부산학교1' CODE_NM, 'SCHOOL10' CODE, 'SCHOOL2' PARENT_CODE FROM DUAL UNION ALL SELECT '부산초등학교' CODE_NM, 'SCHOOL11' CODE, 'SCHOOL10' PARENT_CODE FROM DUAL UNION ALL SELECT '부산초등학교-1' CODE_NM, 'SCHOOL12' CODE, 'SCHOOL11' PARENT_CODE FROM DUAL UNION ALL SELECT '부산학교2' CODE_NM, 'SCHOOL13' CODE, 'SCHOOL2' PARENT_CODE FROM DUAL UNION ALL SELECT '부산중학교' CODE_NM, 'SCHOOL14' CODE, 'SCHOOL13' PARENT_CODE FROM DUAL UNION ALL SELECT '부산중학교-1' CODE_NM, 'SCHOOL15' CODE, 'SCHOOL14' PARENT_CODE FROM DUAL UNION ALL SELECT '부산중학교-2' CODE_NM, 'SCHOOL16' CODE, 'SCHOOL14' PARENT_CODE FROM DUAL UNION ALL SELECT '부산중학교-3' CODE_NM, 'SCHOOL17' CODE, 'SCHOOL14' PARENT_CODE FROM DUAL UNION ALL SELECT '부산고등학교' CODE_NM, 'SCHOOL18' CODE, 'SCHOOL13' PARENT_CODE FROM DUAL UNION ALL SELECT '부산고등학교-1' CODE_NM, 'SCHOOL19' CODE, 'SCHOOL18' PARENT_CODE FROM DUAL UNION ALL SELECT '부산고등학교-2' CODE_NM, 'SCHOOL20' CODE, 'SCHOOL18' PARENT_CODE FROM DUAL UNION ALL SELECT '부산고등학교-3' CODE_NM, 'SCHOOL21' CODE, 'SCHOOL18' PARENT_CODE FROM DUAL UNION ALL SELECT '부산고등학교-4' CODE_NM, 'SCHOOL22' CODE, 'SCHOOL18' PARENT_CODE FROM DUAL ) SELECT CODE , CODE_NM , PARENT_CODE FROM T |
위 쿼리에서
1 2 3 4 5 | START WITH PARENT_CODE IS NULL CONNECT BY PRIOR CODE = PARENT_CODE ORDER SIBLINGS BY CODE |
로 계층형 쿼리를 사용 중 입니다.
하지만 CODE 가 다르지만 동일한 코드명인 '학교' 가 2 개의 ROW 로 나오는데요,
이와 같은 CODE는 다르지만 코드명이 같은것을 하나의 ROW 로 보여주기 위해 계층형 쿼리를 사용하기 전
1 2 3 4 5 6 7 8 9 10 11 | SELECT LISTAGG(CODE, ',' ) CODE , CODE_NM , PARENT_CODE FROM T GROUP BY CODE_NM, PARENT_CODE |
를 사용하여 나온 결과에 계층형을 적용시키니 CODE 와 PARENT_CODE 가 다르게 나와(코드명이 같은 '학교')
계층형 쿼리가 만들어지지 않습니다.
계속 고민하면서 검색도 해봤는데 구현이 되질 않고 있습니다.
어떠한 방법으로 하는게 좋을까요?