Thank you so much on your guidance and help.
Since my replies to the below post don't appear in the Latest posts list, I'm creating a new thread, sorry about it, all the details are available in the below link.
In the same recursive query provided by you, I would like the data sorted by their hierarchy, expecting it as given below.
I'm fine if we need to write multiple queries for this, unfortunately, I'm not allowed to write Stored procedures or functions for this, this is restricted in our environment. Hence, I need to get this through SQL statements alone :(
Emp_ID Emp Name Mgr ID Salary
28000 Mark NULL cumulative salary
28001 Steve 28000 cumulative salary
28003 Lorena 28001 cumulative salary
28005 Patrix 28003 cumulative salary
28006 Zoya 28005 cumulative salary
28004 Simon 28001 cumulative salary
28002 Anna 28000 cumulative salary
28007 Peter 28002 cumulative salary
28008 Paul 280 cumulative salary
To sort the hierarchy you need to calculate the path which leads to each child, this is just another column in your original query. Now join this to the 2nd Select doing the sums and you get:
WITH RECURSIVE REC_EMP_CHILD_sort ( EMP_ID , EMP_NAME , MGR_ID , lvl , Path ) AS ( SELECT A.EMP_ID , A.EMP_NAME , A.MGR_ID , 0
-- path based on EMP_ID , Cast((A.EMP_ID (Format '9(10)')) AS VARCHAR(300)) -- must be long enough to fit all levels in the hierary FROM TMP_EMP_DATA A WHERE A.EMP_NAME = 'Mark' UNION ALL SELECT A.EMP_ID , A.EMP_NAME , A.MGR_ID , B.lvl + 1 , B.Path || '.' || (A.EMP_ID (Format '9(10)')) FROM TMP_EMP_DATA A JOIN REC_EMP_CHILD_sort B ON B.EMP_ID = A.MGR_ID ), RECURSIVE REC_EMP_CHILD ( EMP_ID , MGR_ID , sal ) AS ( SELECT EMP_ID , MGR_ID , Coalesce(SALARY, 0) AS sal FROM TMP_EMP_DATA A WHERE NOT EXISTS ( SELECT * FROM TMP_EMP_DATA AS B WHERE B.MGR_ID = A.EMP_ID ) UNION ALL SELECT p.EMP_ID , p.MGR_ID , c.SAL + Coalesce(p.SALARY,0) FROM TMP_EMP_DATA AS p JOIN REC_EMP_CHILD AS c ON p.EMP_ID = c.MGR_ID ) SELECT t.emp_id , t.emp_name -- to visualize the hierachy use this instead --, Substr('' (CHAR(100)), 1, lvl*2) || t.emp_name -- up to 50 levels , t.mgr_id , t2.sumsal
--, path -- uncomment to see the path FROM REC_EMP_CHILD_sort AS t JOIN ( SELECT emp_id
, Sum(sal) AS sumsal FROM REC_EMP_CHILD GROUP BY 1--,2 ) AS t2 ON t.emp_id = t2.emp_id ORDER BY Path
I don't know if this can be done in a single query (and if this would be more efficient).