혹시 자료 중에 부모는 N 인데 자식은 Y 인 경우는 없나요?
그런 경우가 있다면 결과가 어떻게 나와야 하나요?
예를 들어 다음과 같은 자료가 존재할 경우 결과는 어떻게 나와야 하나요?
D, _, Y
E, D, N
F, E, Y
 

WITH t AS
(
SELECT 'A' cd, '' up_cd, '기준관리' nm, 'Y' yn  FROM dual
UNION ALL SELECT 'A-1'  , 'A'  , '은행관리', 'Y' FROM dual
UNION ALL SELECT 'A-2'  , 'A'  , '계좌관리', 'N' FROM dual
UNION ALL SELECT 'B'    , ''   , '기준관리', 'Y' FROM dual
UNION ALL SELECT 'B-1'  , 'B'  , '은행관리', 'Y' FROM dual
UNION ALL SELECT 'B-1-1', 'B-1', '계좌관리', 'N' FROM dual
UNION ALL SELECT 'C'    , ''   , '기준관리', 'Y' FROM dual
UNION ALL SELECT 'C-1'  , 'C'  , '은행관리', 'N' FROM dual
UNION ALL SELECT 'C-2'  , 'C'  , '계좌관리', 'N' FROM dual
UNION ALL SELECT 'D'    , ''   , '기준관리', 'Y' FROM dual
UNION ALL SELECT 'D-1'  , 'D'  , '은행관리', 'N' FROM dual
UNION ALL SELECT 'D-1-1', 'D-1', '계좌관리', 'Y' FROM dual
)
SELECT cd, up_cd, nm, yn
  FROM t m
 WHERE yn = 'Y'
   AND EXISTS (SELECT 1
                 FROM t s
                WHERE CONNECT_BY_ISLEAF = 1
                  AND yn = 'Y'
                START WITH cd = m.cd
                CONNECT BY PRIOR cd = up_cd
                       AND PRIOR yn = 'Y'
               )
 START WITH up_cd IS NULL
 CONNECT BY PRIOR cd = up_cd
        AND PRIOR yn = 'Y'
;