지금 확인해보니 질문 데이터가 조금 애매하네요.

sys_connect_by_path로 역추적해서 like 검색으로 해결습니다.

음 이런것은 어떠신가요 ?

역방향으로 푼 후에 다시 정방향으로 푼것입니다.

값만 필요하다면 정방향으로 다시 푸실필요는 없을것같고 group by 만 해주면 될것 같습니다.

WITH tmp AS
(
   SELECT '대분류1' as codeName, 'A00' as childCode, '000' as upperCode FROM DUAL
    UNION ALL SELECT '중분류1', 'A01', 'A00' FROM DUAL
    UNION ALL SELECT '소분류1', 'A02', 'A01' FROM DUAL
    UNION ALL SELECT '소분류2', 'A03', 'A02' FROM DUAL
    UNION ALL SELECT '소분류3', 'A04', 'A03' FROM DUAL
    UNION ALL SELECT '소분류4', 'A05', 'A03' FROM DUAL
    UNION ALL SELECT '소분류5', 'A06', 'A03' FROM DUAL
    UNION ALL SELECT '소분류6', 'A07', 'A03' FROM DUAL
    UNION ALL SELECT '중분류2', 'A08', 'A01' FROM DUAL
    UNION ALL SELECT '소분류7', 'A09', 'A08' FROM DUAL
    UNION ALL SELECT '소분류8', 'A10', 'A09' FROM DUAL
    UNION ALL SELECT '소분류9', 'A11', 'A09' FROM DUAL)
SELECT LPAD('-', LEVEL, '-') || LEVEL AS LV  -- 정방향
      , codeName
      ,upperCode
      ,childCode
  FROM ( 
        SELECT  -- 역방향
                codeName
              ,upperCode
              ,childCode
        from tmp
        start with CHILDCODE IN ('A04', 'A05', 'A10')
        CONNECT BY PRIOR  UPPERCODE = CHILDCODE
        GROUP BY codeName
              ,upperCode
              ,childCode
        )
   start with UPPERCODE = '000'
   CONNECT BY PRIOR  CHILDCODE = UPPERCODE