Oracle Magazine, September/October 2018
Database Developer and DBA ETL ORACLE MAGAZINE SEPTEMBER OCTOBER 2018 88 3 where mgr is null EMPNO ENAME MGR 7839 KING I have now discovered the first or top level of the hierarchy Now I must find all employees whose manager is KING I could construct a new query that explicitly references WHERE MGR KING but in general what I need is the output from the query in Listing 2 to become an input into a query to derive the next level of the hierarchy I can do that with a nonhierarchical query by using a standard join In Listing 3 I nest the initial query locating those rows where the manager is null as an inline view into a wrapping query that joins the MGR column to the EMPNO column to get the employees in the next level in the hierarchy This yields the three employees JONES BLAKE and CLARK who report to KING Listing 3 Second level of the hierarchy via a join SQL select e2 empno e2 ename e2 mgr 2 from emp e2 3 select empno mgr 4 from emp 5 where mgr is null inner 6 where e2 mgr inner empno
You must have JavaScript enabled to view digital editions.
