Re: Trouble with hierarchical query

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 20 Jul 2001 18:54:57 -0700
Message-ID: <9jandh02gn1@drn.newsguy.com>

In article <28367dfb.0107191020.4dfcca4e_at_posting.google.com>, vfabro_at_covansys.com says...
>
>I'm having a tough time querying hierarchical data and returning a
>result set in the format I want. Consider the following data (taken
>from Oracle's documentation):
>ORG_CHART EMPNO MGR JOB
>------------ ---------- ---------- ---------
>KING 7839 PRESIDENT
> JONES 7566 7839 MANAGER
> SCOTT 7788 7566 ANALYST
> ADAMS 7876 7788 CLERK
> FORD 7902 7566 ANALYST
> SMITH 7369 7902 CLERK
> BLAKE 7698 7839 MANAGER
> ALLEN 7499 7698 SALESMAN
> WARD 7521 7698 SALESMAN
> MARTIN 7654 7698 SALESMAN
> TURNER 7844 7698 SALESMAN
> JAMES 7900 7698 CLERK
> CLARK 7782 7839 MANAGER
> MILLER 7934 7782 CLERK
>
>I would like to be able to query for a result set that includes 2
>columns: the ancestor from my IN clause (level = 1), and the
>descendant (level > 1). For example, I would like a single query
>where I can specify a where condition to retrieve the children of
>Jones and Clark (such as "emp_no in (7566, 7782)"). It should return
>the following result set:
>7566 7566
>7566 7788
>7566 7876
>7566 7902
>7566 7369
>7782 7782
>7782 7934
>
>I can easily get a list of any one ancestor and his children, but I
>can't get a list of multiple ancestors and their children.
>
>Any ideas? Help!
>
>Thanks,
>Vince

Sigh, no version....

Ok, in 8i and up:

scott_at_ORA817.US.ORACLE.COM> select empno, (select empno

  2                                   from emp e2
  3                                  where empno in ( 7566, 7782 )
  4                                  start with e2.empno = emp.empno
5                                  connect by prior mgr = empno ) top_o_the_tree

  6 from emp
  7 start with empno in ( 7566, 7782 )
  8 connect by prior empno = mgr
  9 /

EMPNO TOP_O_THE_TREE
---------- --------------

      7566           7566
      7788           7566
      7876           7566
      7902           7566
      7369           7566
      7782           7782
      7934           7782

7 rows selected.

in 8.0 and before, you have to hide the select we select in the select list in a PLSQL function.

in 9i, you can:

scott_at_ORA9I.WORLD> select empno, nvl( substr( path, 2, instr(path,'/', 2 )-2 ), substr(path,2)) mgr
2 from ( select empno, sys_connect_by_path( empno, '/' ) path

  3             from emp
  4            start with empno in ( 7566, 7782 )
  5          connect by prior empno = mgr
  6             )

7 /

EMPNO MGR

---------- --------------------
      7566 7566
      7788 7566
      7876 7566
      7902 7566
      7369 7566
      7782 7782
      7934 7782

7 rows selected.

the connect by path returns the path of the tree we took to get to a node:

scott_at_ORA9I.WORLD> select empno, path mgr 2 from ( select empno, sys_connect_by_path( empno, '/' ) path

  3             from emp
  4            start with empno in ( 7566, 7782 )
  5          connect by prior empno = mgr
  6             )

7 /

EMPNO MGR

---------- --------------------
      7566 /7566
      7788 /7566/7788
      7876 /7566/7788/7876
      7902 /7566/7902
      7369 /7566/7902/7369
      7782 /7782
      7934 /7782/7934

7 rows selected.

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp

Received on Fri Jul 20 2001 - 20:54:57 CDT