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 (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',       'true', 'X', null, null, null FROM DUAL;
5 rows affected

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

SELECT DISTINCT *
FROM   location l
START WITH EXISTS(SELECT 1 FROM log WHERE location_id = l.id)
CONNECT BY name = SUBSTR(PRIOR name, 1, INSTR(PRIOR name, '>', -1) - 1)


ID
NAME
IS_ACTIVE
LEVEL1
LEVEL2
LEVEL3
LEVEL4


2
A>B
true
A
B
null
null

1
A
true
A
null
null
null

3
A>B>C
true
A
B
C
null

5
X
true
X
null
null
null


SELECT DISTINCT *
FROM   location l
START WITH EXISTS(SELECT 1 FROM log WHERE location_id = l.id)
CONNECT BY 
       PRIOR level1 = level1
AND    (  (   level2 IS NULL AND level3 IS NULL AND level4 IS NULL
          AND PRIOR level2 IS NOT NULL AND PRIOR level3 IS NULL AND PRIOR level4 IS NULL
          )
          OR (   PRIOR level2 = level2
             AND ( (   level3 IS NULL AND level4 IS NULL
                   AND PRIOR level3 IS NOT NULL AND PRIOR level4 IS NULL
                   )
                 OR (   PRIOR level3 = level3
                    AND level4 IS NULL AND PRIOR level4 IS NOT NULL
                    )
                 )
              )
      )


ID
NAME
IS_ACTIVE
LEVEL1
LEVEL2
LEVEL3
LEVEL4


3
A>B>C
true
A
B
C
null

2
A>B
true
A
B
null
null

1
A
true
A
null
null
null

5
X
true
X
null
null
null

ID	NAME	IS_ACTIVE	LEVEL1	LEVEL2	LEVEL3	LEVEL4
2	A>B	true	A	B	null	null
1	A	true	A	null	null	null
3	A>B>C	true	A	B	C	null
5	X	true	X	null	null	null