Chris,
Pls create this view and use it in your Query.
CREATE OR REPLACE VIEW UV_OBS_LEVELS
(LEVEL1_ID, LEVEL1_NAME, LEVEL1_DEPTH, LEVEL2_ID, LEVEL2_NAME,
LEVEL2_DEPTH, LEVEL3_ID, LEVEL3_NAME, LEVEL3_DEPTH, LEVEL4_ID,
LEVEL4_NAME, LEVEL4_DEPTH, LEVEL5_ID, LEVEL5_NAME, LEVEL5_DEPTH,
LEVEL6_ID, LEVEL6_NAME, LEVEL6_DEPTH, LEVEL7_ID, LEVEL7_NAME,
LEVEL7_DEPTH, LEVEL8_ID, LEVEL8_NAME, LEVEL8_DEPTH, LEVEL9_ID,
LEVEL9_NAME, LEVEL9_DEPTH, LEVEL10_ID, LEVEL10_NAME, LEVEL10_DEPTH,
OBS_PATH)
AS
SELECT A.ID LEVEL1_ID, A.NAME LEVEL1_NAME, A.DEPTH LEVEL1_DEPTH, B.ID LEVEL2_ID, B.NAME LEVEL2_NAME, B.DEPTH LEVEL2_DEPTH
, C.ID LEVEL3_ID, C.NAME LEVEL3_NAME, C.DEPTH LEVEL3_DEPTH, D.ID LEVEL4_ID, D.NAME LEVEL4_NAME, D.DEPTH LEVEL4_DEPTH
, E.ID LEVEL5_ID, E.NAME LEVEL5_NAME, E.DEPTH LEVEL5_DEPTH, F.ID LEVEL6_ID, F.NAME LEVEL6_NAME, F.DEPTH LEVEL6_DEPTH
, G.ID LEVEL7_ID, G.NAME LEVEL7_NAME, G.DEPTH LEVEL7_DEPTH, H.ID LEVEL8_ID, H.NAME LEVEL8_NAME, H.DEPTH LEVEL8_DEPTH
, I.ID LEVEL9_ID, I.NAME LEVEL9_NAME, I.DEPTH LEVEL9_DEPTH, J.ID LEVEL10_ID, J.NAME LEVEL10_NAME, J.DEPTH LEVEL10_DEPTH
, CASE WHEN J.ID IS NOT NULL THEN J.NAME || '/' END || CASE WHEN I.ID IS NOT NULL THEN I.NAME || '/' END
|| CASE WHEN H.ID IS NOT NULL THEN H.NAME || '/' END || CASE WHEN G.ID IS NOT NULL THEN G.NAME || '/' END
|| CASE WHEN F.ID IS NOT NULL THEN F.NAME || '/' END || CASE WHEN E.ID IS NOT NULL THEN E.NAME || '/' END
|| CASE WHEN D.ID IS NOT NULL THEN D.NAME || '/' END || CASE WHEN C.ID IS NOT NULL THEN C.NAME || '/' END
|| CASE WHEN B.ID IS NOT NULL THEN B.NAME || '/' END || CASE WHEN A.ID IS NOT NULL THEN A.NAME || '/' END OBS_PATH
FROM
PRJ_OBS_UNITS A
, PRJ_OBS_UNITS B
, PRJ_OBS_UNITS C
, PRJ_OBS_UNITS D
, PRJ_OBS_UNITS E
, PRJ_OBS_UNITS F
, PRJ_OBS_UNITS G
, PRJ_OBS_UNITS H
, PRJ_OBS_UNITS I
, PRJ_OBS_UNITS J
WHERE
A.PARENT_ID = B.ID(+)
AND B.PARENT_ID = C.ID(+)
AND C.PARENT_ID = D.ID(+)
AND D.PARENT_ID = E.ID(+)
AND E.PARENT_ID = F.ID(+)
AND F.PARENT_ID = G.ID(+)
AND G.PARENT_ID = H.ID(+)
AND H.PARENT_ID = I.ID(+)
AND I.PARENT_ID = J.ID(+)
/
Regards,
sundar