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 |