CODEWITHSUNDEEP
sqloracle-databasehierarchyhierarchical-data
Jose L Martinez-Avial
Jose L Martinez-Avial

Reputation: 2265

Get all subpaths in a hierarchical query in Oracle

I've a table that contains a list of sales departments as follows:

+-------+---------------+-----------+
|DEPT_ID|DEPT_NAME      |DEPT_PARENT|
+-------+---------------+-----------+
|5500   |World          |           |
|5510   |Region 1       |5500       |
|5511   |Cell 1 Region 1|5510       |
|5512   |Cell 2 Region 1|5510       |
|5513   |Cell 3 Region 1|5510       |
|5514   |Cell 4 Region 1|5510       |
|5515   |Cell 5 Region 1|5510       |
|5520   |Region 2       |5500       |
|5521   |Cell 1 Region 2|5520       |
|5522   |Cell 2 Region 2|5520       |
|5530   |Region 3       |5500       |
|5531   |Cell 1 Region 3|5530       |
|5532   |Cell 2 Region 3|5530       |
|5540   |Region 4       |5500       |
|5533   |Cell 1 Region 4|5540       |
|5534   |Cell 2 Region 4|5533       |
|5590   |Region 5       |5500       |
|5591   |Cell 1 Region 5|5590       |
+-------+---------------+-----------+

I need a query that returns all possible subpaths. So for the first three rows it should be as follows:

5500 5510
5500 5511
5510 5511

so for each possible subpath it would return the first and last department of the path. It is fairly easy to get the path by doing this:

    SELECT d.*, LTRIM (SYS_CONNECT_BY_PATH (dept_id, '-'), '-') AS PATH
      FROM depts d
START WITH dept_parent IS NULL
CONNECT BY PRIOR dept_id = dept_parent

but how I can get all possible subpaths?

Upvotes: 1

Views: 95

Answers (2)

MT0
MT0

Reputation: 168051

Use CONNECT_BY_ROOT to find the start of the path.

Oracle Setup:

CREATE TABLE depts ( DEPT_ID, DEPT_NAME, DEPT_PARENT ) AS
SELECT 5500, 'World',           NULL FROM DUAL UNION ALL
SELECT 5510, 'Region 1',        5500 FROM DUAL UNION ALL
SELECT 5511, 'Cell 1 Region 1', 5510 FROM DUAL UNION ALL
SELECT 5512, 'Cell 2 Region 1', 5510 FROM DUAL UNION ALL
SELECT 5513, 'Cell 3 Region 1', 5510 FROM DUAL UNION ALL
SELECT 5514, 'Cell 4 Region 1', 5510 FROM DUAL UNION ALL
SELECT 5515, 'Cell 5 Region 1', 5510 FROM DUAL UNION ALL
SELECT 5520, 'Region 2',        5500 FROM DUAL UNION ALL
SELECT 5521, 'Cell 1 Region 2', 5520 FROM DUAL UNION ALL
SELECT 5522, 'Cell 2 Region 2', 5520 FROM DUAL UNION ALL
SELECT 5530, 'Region 3',        5500 FROM DUAL UNION ALL
SELECT 5531, 'Cell 1 Region 3', 5530 FROM DUAL UNION ALL
SELECT 5532, 'Cell 2 Region 3', 5530 FROM DUAL UNION ALL
SELECT 5540, 'Region 4',        5500 FROM DUAL UNION ALL
SELECT 5533, 'Cell 1 Region 4', 5540 FROM DUAL UNION ALL
SELECT 5534, 'Cell 2 Region 4', 5533 FROM DUAL UNION ALL
SELECT 5590, 'Region 5',        5500 FROM DUAL UNION ALL
SELECT 5591, 'Cell 1 Region 5', 5590 FROM DUAL;

Query:

SELECT CONNECT_BY_ROOT( dept_parent ) AS ancestor,
       dept_id,
       SYS_CONNECT_BY_PATH( dept_parent, '>' ) || '>' || dept_id AS path
FROM   depts
START WITH dept_parent IS NOT NULL
CONNECT BY PRIOR dept_id = dept_parent;

Output:

ANCESTOR | DEPT_ID | PATH                
-------: | ------: | :-------------------
    5500 |    5510 | >5500>5510          
    5500 |    5511 | >5500>5510>5511     
    5500 |    5512 | >5500>5510>5512     
    5500 |    5513 | >5500>5510>5513     
    5500 |    5514 | >5500>5510>5514     
    5500 |    5515 | >5500>5510>5515     
    5500 |    5520 | >5500>5520          
    5500 |    5521 | >5500>5520>5521     
    5500 |    5522 | >5500>5520>5522     
    5500 |    5530 | >5500>5530          
    5500 |    5531 | >5500>5530>5531     
    5500 |    5532 | >5500>5530>5532     
    5500 |    5540 | >5500>5540          
    5500 |    5533 | >5500>5540>5533     
    5500 |    5534 | >5500>5540>5533>5534
    5500 |    5590 | >5500>5590          
    5500 |    5591 | >5500>5590>5591     
    5510 |    5511 | >5510>5511          
    5510 |    5512 | >5510>5512          
    5510 |    5513 | >5510>5513          
    5510 |    5514 | >5510>5514          
    5510 |    5515 | >5510>5515          
    5520 |    5521 | >5520>5521          
    5520 |    5522 | >5520>5522          
    5530 |    5531 | >5530>5531          
    5530 |    5532 | >5530>5532          
    5533 |    5534 | >5533>5534          
    5540 |    5533 | >5540>5533          
    5540 |    5534 | >5540>5533>5534     
    5590 |    5591 | >5590>5591

db<>fiddle here

Upvotes: 1

GMB
GMB

Reputation: 222502

I understand that you want to generate a closure table, that contains all possible paths in the hierarchy.

Here is a solution that uses a standard recursive query to achieve that. I also added a column that stores the depth of the relationship, since this information is often useful in closure tables.

with cte(node_id, dept_id, dept_parent, lvl) as (
    select dept_id node_id, dept_id, dept_parent, 0 lvl from dept
    union all 
    select c.node_id, d.dept_id, d.dept_parent, c.lvl + 1
    from cte c
    inner join dept d on d.dept_id = c.dept_parent
)
select dept_id ancestor, node_id node, lvl 
from cte 
where lvl > 0
order by node, ancestor

This demo on DB Fiddle with your sample data produces 30 rows, whose first 10 are:

ANCESTOR | NODE | LVL
-------: | ---: | --:
    5500 | 5510 |   1
    5500 | 5511 |   2
    5510 | 5511 |   1
    5500 | 5512 |   2
    5510 | 5512 |   1
    5500 | 5513 |   2
    5510 | 5513 |   1
    5500 | 5514 |   2
    5510 | 5514 |   1
    5500 | 5515 |   2

Upvotes: 0

Related Questions