I have a data set that represents a set of process steps to create a set of products. Each product has a step sequence defined in the data set, as FromStep and ToStep. I'm trying to use a hierarchical query to pull the process steps for all products but I'm clearly missing something because it's not working. Any advice on where I've gone wrong would be hugely appreciated (perhaps this isn't even the way to approach this task). I've tried to create a minimal example of my problem below - my real data set is much larger than this.
Create a sample table:
CREATE TABLE HIER_TEST
(
PRODUCT VARCHAR2(26 BYTE),
STEPNAME VARCHAR2(26 BYTE),
STEPID NUMBER(4,0),
FROMSTEP NUMBER(4,0),
TOSTEP NUMBER(4,0)
) ;
Add some data:
Insert into HIER_TEST (PRODUCT,STEPNAME,STEPID,FROMSTEP,TOSTEP) values ('Product1','Step1',1,1,2);
Insert into HIER_TEST (PRODUCT,STEPNAME,STEPID,FROMSTEP,TOSTEP) values ('Product1','Step2',2,2,3);
Insert into HIER_TEST (PRODUCT,STEPNAME,STEPID,FROMSTEP,TOSTEP) values ('Product1','Step3',3,3,4);
Insert into HIER_TEST (PRODUCT,STEPNAME,STEPID,FROMSTEP,TOSTEP) values ('Product1','Step4',4,4,5);
Insert into HIER_TEST (PRODUCT,STEPNAME,STEPID,FROMSTEP,TOSTEP) values ('Product1','Step5',5,5,6);
Insert into HIER_TEST (PRODUCT,STEPNAME,STEPID,FROMSTEP,TOSTEP) values ('Product2','Step1',1,1,2);
Insert into HIER_TEST (PRODUCT,STEPNAME,STEPID,FROMSTEP,TOSTEP) values ('Product2','Step2',2,2,3);
Insert into HIER_TEST (PRODUCT,STEPNAME,STEPID,FROMSTEP,TOSTEP) values ('Product2','Step3',3,3,4);
Insert into HIER_TEST (PRODUCT,STEPNAME,STEPID,FROMSTEP,TOSTEP) values ('Product2','Step4',4,4,5);
Insert into HIER_TEST (PRODUCT,STEPNAME,STEPID,FROMSTEP,TOSTEP) values ('Product2','Step5',5,5,6);
Insert into HIER_TEST (PRODUCT,STEPNAME,STEPID,FROMSTEP,TOSTEP) values ('Product5','Step1',1,1,2);
Insert into HIER_TEST (PRODUCT,STEPNAME,STEPID,FROMSTEP,TOSTEP) values ('Product5','Step2',2,2,3);
Insert into HIER_TEST (PRODUCT,STEPNAME,STEPID,FROMSTEP,TOSTEP) values ('Product5','Step3',3,3,4);
Insert into HIER_TEST (PRODUCT,STEPNAME,STEPID,FROMSTEP,TOSTEP) values ('Product5','Step4',4,4,5);
Insert into HIER_TEST (PRODUCT,STEPNAME,STEPID,FROMSTEP,TOSTEP) values ('Product5','Step5',5,5,6);
I'm then trying this query:
select
level,
connect_by_isleaf leafnode,
hier_test.*,
connect_by_root stepid as rootItem
from
hier_test
start with
stepid = 1
connect by nocycle prior
tostep = fromstep
order siblings by
product,
stepid
;
which returns:
+-------+-------+--------------+-----------+-------+-------+-------+-------+
| Level | LfNd | Product | StepName | StepId| FrStp | ToStp | rtItm |
+-------+-------+--------------+-----------+-------+-------+-------+-------+
| 1 | 0 | Product1 | Step1 | 1 | 1 | 2 | 1 |
| 2 | 0 | Product1 | Step2 | 2 | 2 | 3 | 1 |
| 3 | 0 | Product1 | Step3 | 3 | 3 | 4 | 1 |
| 4 | 0 | Product1 | Step4 | 4 | 4 | 5 | 1 |
| 5 | 1 | Product1 | Step5 | 5 | 5 | 6 | 1 |
| 5 | 1 | Product2 | Step5 | 5 | 5 | 6 | 1 |
| 5 | 1 | Product5 | Step5 | 5 | 5 | 6 | 1 |
| 4 | 0 | Product2 | Step4 | 4 | 4 | 5 | 1 |
| 5 | 1 | Product1 | Step5 | 5 | 5 | 6 | 1 |
| 5 | 1 | Product2 | Step5 | 5 | 5 | 6 | 1 |
| 5 | 1 | Product5 | Step5 | 5 | 5 | 6 | 1 |
| 4 | 0 | Product5 | Step4 | 4 | 4 | 5 | 1 |
| 5 | 1 | Product1 | Step5 | 5 | 5 | 6 | 1 |
| 5 | 1 | Product2 | Step5 | 5 | 5 | 6 | 1 |
| 5 | 1 | Product5 | Step5 | 5 | 5 | 6 | 1 |
| 3 | 0 | Product2 | Step3 | 3 | 3 | 4 | 1 |
| 4 | 0 | Product1 | Step4 | 4 | 4 | 5 | 1 |
| 5 | 1 | Product1 | Step5 | 5 | 5 | 6 | 1 |
| 5 | 1 | Product2 | Step5 | 5 | 5 | 6 | 1 |
| 5 | 1 | Product5 | Step5 | 5 | 5 | 6 | 1 |
This appears to be locked in a loop forever...
I was expecting the order siblings by clause to sort the table by product then step number, but the results aren't showing this.
What I was aiming to get was this:
+-------+-------+--------------+-----------+-------+-------+-------+-------+
| Level | LfNd | Product | StepName | StepId| FrStp | ToStp | rtItm |
+-------+-------+--------------+-----------+-------+-------+-------+-------+
| 1 | 0 | Product1 | Step1 | 1 | 1 | 2 | 1 |
| 2 | 0 | Product1 | Step2 | 2 | 2 | 3 | 1 |
| 3 | 0 | Product1 | Step3 | 3 | 3 | 4 | 1 |
| 4 | 0 | Product1 | Step4 | 4 | 4 | 5 | 1 |
| 5 | 1 | Product1 | Step5 | 5 | 5 | 6 | 1 |
| 1 | 0 | Product2 | Step1 | 1 | 1 | 2 | 1 |
| 2 | 0 | Product2 | Step2 | 2 | 2 | 3 | 1 |
| 3 | 0 | Product2 | Step3 | 3 | 3 | 4 | 1 |
| 4 | 0 | Product2 | Step4 | 4 | 4 | 5 | 1 |
| 5 | 1 | Product2 | Step5 | 5 | 5 | 6 | 1 |
| 1 | 0 | Product5 | Step1 | 1 | 1 | 2 | 1 |
| 2 | 0 | Product5 | Step2 | 2 | 2 | 3 | 1 |
| 3 | 0 | Product5 | Step3 | 3 | 3 | 4 | 1 |
| 4 | 0 | Product5 | Step4 | 4 | 4 | 5 | 1 |
| 5 | 1 | Product5 | Step5 | 5 | 5 | 6 | 1 |
I'm sure this will be obvious but I've found nothing that really helps me when trying to find a solution.
Thanks in advance.
The results are showing it.
CREATE TABLE HIER_TEST
(
PRODUCT VARCHAR2(26 BYTE),
STEPNAME VARCHAR2(26 BYTE),
STEPID NUMBER(4,0),
FROMSTEP NUMBER(4,0),
TOSTEP NUMBER(4,0)
);
Taking a smaller sub-set of your data so it is easier to visualise:
Insert into HIER_TEST (PRODUCT,STEPNAME,STEPID,FROMSTEP,TOSTEP)
SELECT 'Product1','Step1',1,1,2 FROM DUAL UNION ALL
SELECT 'Product1','Step2',2,2,3 FROM DUAL UNION ALL
SELECT 'Product1','Step3',3,3,4 FROM DUAL UNION ALL
SELECT 'Product2','Step1',1,1,2 FROM DUAL UNION ALL
SELECT 'Product2','Step2',2,2,3 FROM DUAL UNION ALL
SELECT 'Product2','Step3',3,3,4 FROM DUAL UNION ALL
SELECT 'Product5','Step1',1,1,2 FROM DUAL UNION ALL
SELECT 'Product5','Step2',2,2,3 FROM DUAL UNION ALL
SELECT 'Product5','Step3',3,3,4 FROM DUAL;
Then your query with an added SYS_CONNECT_BY_PATH( Product, ', ' ) (you do not need the NOCYCLE clause as there aren't any cycles in your data):
select level,
connect_by_isleaf leaf,
product,
stepname AS sname,
stepid AS id,
fromstep AS fs,
tostep AS ts,
connect_by_root stepid as rt,
SYS_CONNECT_BY_PATH( product, ', ' ) As path
from hier_test
start with
stepid = 1
connect by
prior tostep = fromstep
order siblings by
product,
stepid
Gives the output:
LEVEL | LEAF | PRODUCT | SNAME | ID | FS | TS | RT | PATH
----: | ---: | :------- | :---- | -: | -: | -: | -: | :-----------------------------
1 | 0 | Product1 | Step1 | 1 | 1 | 2 | 1 | , Product1
2 | 0 | Product1 | Step2 | 2 | 2 | 3 | 1 | , Product1, Product1
3 | 1 | Product1 | Step3 | 3 | 3 | 4 | 1 | , Product1, Product1, Product1
3 | 1 | Product2 | Step3 | 3 | 3 | 4 | 1 | , Product1, Product1, Product2
3 | 1 | Product5 | Step3 | 3 | 3 | 4 | 1 | , Product1, Product1, Product5
2 | 0 | Product2 | Step2 | 2 | 2 | 3 | 1 | , Product1, Product2
3 | 1 | Product1 | Step3 | 3 | 3 | 4 | 1 | , Product1, Product2, Product1
3 | 1 | Product2 | Step3 | 3 | 3 | 4 | 1 | , Product1, Product2, Product2
3 | 1 | Product5 | Step3 | 3 | 3 | 4 | 1 | , Product1, Product2, Product5
2 | 0 | Product5 | Step2 | 2 | 2 | 3 | 1 | , Product1, Product5
3 | 1 | Product1 | Step3 | 3 | 3 | 4 | 1 | , Product1, Product5, Product1
3 | 1 | Product2 | Step3 | 3 | 3 | 4 | 1 | , Product1, Product5, Product2
3 | 1 | Product5 | Step3 | 3 | 3 | 4 | 1 | , Product1, Product5, Product5
1 | 0 | Product2 | Step1 | 1 | 1 | 2 | 1 | , Product2
2 | 0 | Product1 | Step2 | 2 | 2 | 3 | 1 | , Product2, Product1
3 | 1 | Product1 | Step3 | 3 | 3 | 4 | 1 | , Product2, Product1, Product1
3 | 1 | Product2 | Step3 | 3 | 3 | 4 | 1 | , Product2, Product1, Product2
3 | 1 | Product5 | Step3 | 3 | 3 | 4 | 1 | , Product2, Product1, Product5
2 | 0 | Product2 | Step2 | 2 | 2 | 3 | 1 | , Product2, Product2
3 | 1 | Product1 | Step3 | 3 | 3 | 4 | 1 | , Product2, Product2, Product1
3 | 1 | Product2 | Step3 | 3 | 3 | 4 | 1 | , Product2, Product2, Product2
3 | 1 | Product5 | Step3 | 3 | 3 | 4 | 1 | , Product2, Product2, Product5
2 | 0 | Product5 | Step2 | 2 | 2 | 3 | 1 | , Product2, Product5
3 | 1 | Product1 | Step3 | 3 | 3 | 4 | 1 | , Product2, Product5, Product1
3 | 1 | Product2 | Step3 | 3 | 3 | 4 | 1 | , Product2, Product5, Product2
3 | 1 | Product5 | Step3 | 3 | 3 | 4 | 1 | , Product2, Product5, Product5
1 | 0 | Product5 | Step1 | 1 | 1 | 2 | 1 | , Product5
2 | 0 | Product1 | Step2 | 2 | 2 | 3 | 1 | , Product5, Product1
3 | 1 | Product1 | Step3 | 3 | 3 | 4 | 1 | , Product5, Product1, Product1
3 | 1 | Product2 | Step3 | 3 | 3 | 4 | 1 | , Product5, Product1, Product2
3 | 1 | Product5 | Step3 | 3 | 3 | 4 | 1 | , Product5, Product1, Product5
2 | 0 | Product2 | Step2 | 2 | 2 | 3 | 1 | , Product5, Product2
3 | 1 | Product1 | Step3 | 3 | 3 | 4 | 1 | , Product5, Product2, Product1
3 | 1 | Product2 | Step3 | 3 | 3 | 4 | 1 | , Product5, Product2, Product2
3 | 1 | Product5 | Step3 | 3 | 3 | 4 | 1 | , Product5, Product2, Product5
2 | 0 | Product5 | Step2 | 2 | 2 | 3 | 1 | , Product5, Product5
3 | 1 | Product1 | Step3 | 3 | 3 | 4 | 1 | , Product5, Product5, Product1
3 | 1 | Product2 | Step3 | 3 | 3 | 4 | 1 | , Product5, Product5, Product2
3 | 1 | Product5 | Step3 | 3 | 3 | 4 | 1 | , Product5, Product5, Product5
Then looking at the path, you can see that it is ordered by the sibling at the first level, then at the second level and then at the third level. It is becuse you are only looking at the product (etc) at the current depth that you are not seeing this ordering.
If you want to restrict it to having the same product as at the previous level then add that to the CONNECT BY clause:
select level,
connect_by_isleaf leaf,
product,
stepname AS sname,
stepid AS id,
fromstep AS fs,
tostep AS ts,
connect_by_root stepid as rt,
SYS_CONNECT_BY_PATH( product, ', ' ) As path
from hier_test
start with
stepid = 1
connect by
prior tostep = fromstep
and prior product = product
order siblings by
product,
stepid
Which outputs:
LEVEL | LEAF | PRODUCT | SNAME | ID | FS | TS | RT | PATH
----: | ---: | :------- | :---- | -: | -: | -: | -: | :-----------------------------
1 | 0 | Product1 | Step1 | 1 | 1 | 2 | 1 | , Product1
2 | 0 | Product1 | Step2 | 2 | 2 | 3 | 1 | , Product1, Product1
3 | 1 | Product1 | Step3 | 3 | 3 | 4 | 1 | , Product1, Product1, Product1
1 | 0 | Product2 | Step1 | 1 | 1 | 2 | 1 | , Product2
2 | 0 | Product2 | Step2 | 2 | 2 | 3 | 1 | , Product2, Product2
3 | 1 | Product2 | Step3 | 3 | 3 | 4 | 1 | , Product2, Product2, Product2
1 | 0 | Product5 | Step1 | 1 | 1 | 2 | 1 | , Product5
2 | 0 | Product5 | Step2 | 2 | 2 | 3 | 1 | , Product5, Product5
3 | 1 | Product5 | Step3 | 3 | 3 | 4 | 1 | , Product5, Product5, Product5
db<>fiddle here