Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE location_master (id, Name, is_active, level1, level2, level3, level4) AS
SELECT 1, 'A',       'true', 'A', null, null, null FROM DUAL UNION ALL
SELECT 2, 'A>B',     'true', 'A', 'B',  null, null FROM DUAL UNION ALL
SELECT 3, 'A>B>C',   'true', 'A', 'B',  'C',  null FROM DUAL UNION ALL
SELECT 4, 'A>B>C>D', 'true', 'A', 'B',  'C',  'D'  FROM DUAL UNION ALL
SELECT 5, 'X>E',     'true', 'A', 'E',  NULL, NULL FROM DUAL UNION ALL
SELECT 6, 'X>E>C',   'true', 'A', 'E',  'C',  NULL FROM DUAL UNION ALL
SELECT 7, 'X',   'true', 'A', 'E',  'C',  NULL FROM DUAL UNION ALL
SELECT 8, 'X>E>C>D', 'true', 'A', 'E',  'C',  'D'  FROM DUAL;
8 rows affected

CREATE TABLE log_response (id, Name, location_id) AS
SELECT 1, 'Log 001', 3 FROM DUAL UNION ALL
SELECT 2, 'Log 002', 5 FROM DUAL;
2 rows affected

SELECT *
FROM   location_master
START WITH id IN (SELECT DISTINCT location_id FROM log_response)
CONNECT BY PRIOR name
           = name || '>' || COALESCE(PRIOR level4, PRIOR level3, PRIOR level2)
ORDER BY NAME


ID
NAME
IS_ACTIVE
LEVEL1
LEVEL2
LEVEL3
LEVEL4


1
A
true
A
null
null
null

2
A>B
true
A
B
null
null

3
A>B>C
true
A
B
C
null

7
X
true
A
E
C
null

5
X>E
true
A
E
null
null


explain plan for 
SELECT *
FROM   location_master
START WITH id IN (SELECT DISTINCT location_id FROM log_response)
CONNECT BY name = SUBSTR(PRIOR name, 1, INSTR(PRIOR name, '>', -1) - 1)
ORDER BY NAME
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);


PLAN_TABLE_OUTPUT


Plan hash value: 1854579171

 

-------------------------------------------------------------------------------------------------

| Id  | Operation                     | Name            | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT              |                 |     4 |   132 |    10  (10)| 00:00:01 |

|   1 |  SORT ORDER BY                |                 |     4 |   132 |    10  (10)| 00:00:01 |

|*  2 |   CONNECT BY WITHOUT FILTERING|                 |       |       |            |          |

|*  3 |    HASH JOIN SEMI             |                 |     2 |    46 |     6   (0)| 00:00:01 |

|   4 |     TABLE ACCESS FULL         | LOCATION_MASTER |     8 |   160 |     3   (0)| 00:00:01 |

|   5 |     TABLE ACCESS FULL         | LOG_RESPONSE    |     2 |     6 |     3   (0)| 00:00:01 |

|   6 |    TABLE ACCESS FULL          | LOCATION_MASTER |     8 |   160 |     3   (0)| 00:00:01 |

-------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - filter("LOCATION_MASTER"."NAME"=SUBSTR(PRIOR

              "LOCATION_MASTER"."NAME",1,INSTR(PRIOR "LOCATION_MASTER"."NAME",'>',(-1))-1))

   3 - access("ID"="LOCATION_ID")


-- SELECT *
-- FROM   location_master t
-- WHERE  EXISTS(
--          SELECT 1
--          FROM   location_master x
--          WHERE  x.name LIKE t.name || '%'
--          AND    x.id IN (3,5)
--        )
-- ORDER BY t.NAME
ORA-00900: invalid SQL statement

-- SELECT *
-- FROM   table_name t
-- WHERE  EXISTS(
--          SELECT 1
--          FROM   table_name x
--          WHERE  ( t.level4 = x.level4 OR t.level4 IS NULL )
--          AND    ( t.level3 = x.level3 OR t.level3 IS NULL )
--          AND    ( t.level2 = x.level2 OR t.level2 IS NULL )
--          AND    t.level1 = x.level1
--          AND    x.id = 3
--        )
ORA-00900: invalid SQL statement

-- SELECT *
-- FROM   table_name
-- START WITH id = 3
-- CONNECT BY PRIOR name
--            = name || '>' || COALESCE(PRIOR level4, PRIOR level3, PRIOR level2)
ORA-00900: invalid SQL statement

-- SELECT *
-- FROM   table_name
-- START WITH id = 3
-- CONNECT BY name = SUBSTR(PRIOR name, 1, INSTR(PRIOR name, '>', -1) - 1)
ORA-00900: invalid SQL statement
ID	NAME	IS_ACTIVE	LEVEL1	LEVEL2	LEVEL3	LEVEL4
1	A	true	A	null	null	null
2	A>B	true	A	B	null	null
3	A>B>C	true	A	B	C	null
7	X	true	A	E	C	null
5	X>E	true	A	E	null	null