안녕하세요..저번에 마농님의 도움으로 쿼리를 잘 응용해서 사용하고 있습니다..감사합니다..

http://www.gurubee.net/article/85161

그런데 이번에는 트리구조에서 하위가 검색대상이었을 경우 상위가 있을시 함께 표시를 해주고 싶은데요..

WITH MST_LST AS (
SELECT 'AA' CD, 'A상점' NM, 'G' GROUP_TYPE, 'F' CD_TYPE, 1 SEQ FROM DUAL UNION ALL 
SELECT 'BB' CD, 'B상점' NM, 'M' GROUP_TYPE, 'F' CD_TYPE, 2 SEQ FROM DUAL UNION ALL
SELECT 'CC' CD, 'C상점' NM, 'G' GROUP_TYPE, 'V' CD_TYPE, 3 SEQ FROM DUAL UNION ALL
SELECT 'DD' CD, 'D상점' NM, 'G' GROUP_TYPE, 'F' CD_TYPE, 4 SEQ FROM DUAL UNION ALL
SELECT 'A' CD, '사과'   NM, 'S' GROUP_TYPE, 'F' CD_TYPE, 1 SEQ FROM DUAL UNION ALL
SELECT 'B' CD, '배'     NM, 'S' GROUP_TYPE, 'F' CD_TYPE, 2 SEQ FROM DUAL UNION ALL
SELECT 'C' CD, '복숭아' NM, 'S' GROUP_TYPE, 'F' CD_TYPE, 3 SEQ FROM DUAL UNION ALL
SELECT 'D' CD, '바나나' NM, 'S' GROUP_TYPE, 'F' CD_TYPE, 4 SEQ FROM DUAL UNION ALL
SELECT 'E' CD, '배추'   NM, 'S' GROUP_TYPE, 'V' CD_TYPE, 1 SEQ FROM DUAL UNION ALL
SELECT 'F' CD, '무우'   NM, 'S' GROUP_TYPE, 'V' CD_TYPE, 2 SEQ FROM DUAL UNION ALL
SELECT 'G' CD, '당근'   NM, 'S' GROUP_TYPE, 'V' CD_TYPE, 3 SEQ FROM DUAL
)
, MST_GROUP AS (
SELECT 'AA' GROUP_CD, 'A' SUB_CD FROM DUAL UNION ALL
SELECT 'AA' GROUP_CD, 'B' SUB_CD FROM DUAL UNION ALL
SELECT 'AA' GROUP_CD, 'C' SUB_CD FROM DUAL UNION ALL
SELECT 'AA' GROUP_CD, 'D' SUB_CD FROM DUAL UNION ALL
SELECT 'BB' GROUP_CD, 'C' SUB_CD FROM DUAL UNION ALL
SELECT 'BB' GROUP_CD, 'D' SUB_CD FROM DUAL UNION ALL
SELECT 'CC' GROUP_CD, 'E' SUB_CD FROM DUAL UNION ALL
SELECT 'CC' GROUP_CD, 'G' SUB_CD FROM DUAL UNION ALL
SELECT 'DD' GROUP_CD, 'BB' SUB_CD FROM DUAL UNION ALL
SELECT 'DD' GROUP_CD, 'A' SUB_CD FROM DUAL
)
SELECT * FROM (

SELECT SUBSTR(SYS_CONNECT_BY_PATH(A.CD,'/'), 2) AS CD_PATH -- 경로
     , LEVEL AS TREE_LV
     , A.CD_TYPE
     , CONNECT_BY_ROOT A.CD AS GROUP_CD
     , CONNECT_BY_ROOT A.NM AS GROUP_NM
     , A.CD
     , A.NM
     , LPAD(' ',4*(LEVEL-1))||A.NM AS NM_LV
     , A.SEQ
     , DECODE(A.GROUP_TYPE, 'S', DECODE(B.GROUP_CD, NULL, 2, 1), 1) KEY1
     , DECODE(A.GROUP_TYPE, 'S', DECODE(B.GROUP_CD, NULL, 3, 2), 2) KEY2
     , B.GROUP_CD AS SUB_GROUP_CD
 FROM MST_LST A
    , MST_GROUP B
WHERE A.CD = B.SUB_CD(+)
START WITH A.GROUP_TYPE IN ('G', 'M') OR B.GROUP_CD IS NULL  -- 그룹 및 하위표시(그룹에 그룹 풀어서 표시) + 그룹에 속하지 않은 하위 표시
CONNECT BY PRIOR A.CD = B.GROUP_CD
ORDER BY A.CD_TYPE
       , DECODE(A.GROUP_TYPE, 'S', DECODE(B.GROUP_CD, NULL, 2, 1), 1)
       , DECODE(A.GROUP_TYPE, 'S', DECODE(B.GROUP_CD, NULL, 3, 2), 2)
       , CONNECT_BY_ROOT A.CD
       , SYS_CONNECT_BY_PATH(LPAD(A.SEQ, 5, '0')||A.CD,'/') -- 그룹에 그룹인 경우 정렬
       , B.GROUP_CD NULLS FIRST
       , A.SEQ
       , A.CD
) T
--WHERE NM LIKE '%바나나%'
;

원본결과

1. 바나나를 검색시 (상위존재시 함께 표시)

2. 무우 검색시 (상위 미존재인 경우)