Oracle Magazine, September/October 2018
Database Developer and DBA ETL The recursive CTE syntax allows for some additional facilities in terms of how the hierarchy should be traversed You can navigate any hierarchy either by taking slices in a top down approach or by simply working down along a branch until all the leaves are exhausted and then working back up through the branches in the same way someone might navigate through a maze Each mechanism is available to developers using the SEARCH clause in the recursive CTE definition I can search breadth first or depth first as shown in Listing 16 with the ordering of the results achieved by the SET clause which nominates a new column that yields a sequence number defining the order in which the rows should be presented Listing 16 Recursive CTE SEARCH and SET clause SQL with each_ level empno name hiredate mgr as 2 select empno ename hiredate mgr from emp 3 where ename KING 4 union all 5 select e empno 6 each_ level name e ename e hiredate e mgr 7 from emp e each_ level 8 where e mgr each_ level empno 9 SEARCH BREADTH FIRST BY HIREDATE SET IDX 10 select name hiredate idx from each_ level NAME HIREDATE IDX KING 17 NOV 81 1 KING JONES 02 APR 81 2 ORACLE MAGAZINE SEPTEMBER OCTOBER 2018 102
You must have JavaScript enabled to view digital editions.
