Additional info needed:
The explanation of the task here doesn't match the sample data and expected result ....
-- S a m p l e D a t a :
Create Table tbl AS
Select 'TASK3' as DEPENDENT, 'TASK2' as TASK From Dual Union All
Select 'TASK1', 'TASK5' From Dual Union All
Select 'TASK2', 'TASK5' From Dual Union All
Select 'TASK4', 'TASK3' From Dual;
DEPENDENT | TASK |
---|---|
TASK3 | TASK2 |
TASK1 | TASK5 |
TASK2 | TASK5 |
TASK4 | TASK3 |
The dependent task must always finish before the Task It seems that something is missing as additional logic to the above statement in order to clarify the issue ...
If quoted is true - then basic paths (orders) from dependant to task (with dependant suborder if any) are:
WITH
row_paths AS
( Select t.DEPENDENT, t.TASK,
t.DEPENDENT || '-' || t.TASK as TASK_ORDER,
o.DEPENDENT || '-' || o.TASK as TASK_SUBORDER
From tbl t
Left Join tbl o ON( o.TASK = t.DEPENDENT )
)
Select * From row_paths Order By TASK Desc, DEPENDENT
DEPENDENT | TASK | TASK_ORDER | TASK_SUBORDER |
---|---|---|---|
TASK1 | TASK5 | TASK1-TASK5 | - |
TASK2 | TASK5 | TASK2-TASK5 | TASK3-TASK2 |
TASK4 | TASK3 | TASK4-TASK3 | - |
TASK3 | TASK2 | TASK3-TASK2 | TASK4-TASK3 |
... if we replace dependant with coresponding path then the leaf to root paths would be like below ...
WITH
row_paths AS
( Select t.DEPENDENT, t.TASK,
t.DEPENDENT || '-' || t.TASK as TASK_ORDER,
o.DEPENDENT || '-' || o.TASK as TASK_SUBORDER
From tbl t
Left Join tbl o ON( o.TASK = t.DEPENDENT )
)
Select rp.*,
Case When rp.TASK_SUBORDER != '-'
Then REPLACE(rp.TASK_ORDER, rp.DEPENDENT, TASK_SUBORDER)
Else rp.TASK_ORDER
End as SELF_LEAF_TO_ROOT_PATH
From row_paths rp
Order By rp.TASK Desc, rp.DEPENDENT
DEPENDENT | TASK | TASK_ORDER | TASK_SUBORDER | SELF_LEAF_TO_ROOT_PATH |
---|---|---|---|---|
TASK1 | TASK5 | TASK1-TASK5 | - | TASK1-TASK5 |
TASK2 | TASK5 | TASK2-TASK5 | TASK3-TASK2 | TASK3-TASK2-TASK5 |
TASK4 | TASK3 | TASK4-TASK3 | - | TASK4-TASK3 |
TASK3 | TASK2 | TASK3-TASK2 | TASK4-TASK3 | TASK4-TASK3-TASK2 |
... please provide some additional explanation of the logic that should be applied to fetch you the expected result ...