Home » SQL & PL/SQL » SQL & PL/SQL » SQL CHALLENGE TO THE EXPERTS OUT THERE
SQL CHALLENGE TO THE EXPERTS OUT THERE [message #184735] Thu, 27 July 2006 11:02 Go to next message
bluezulu
Messages: 3
Registered: July 2006
Location: UK
Junior Member
Hi,
I have searched through all the forums I know of but have not been able to find an example of this. It is a hierarchical query but with a difference. Using the SCOTT schema and the EMP table as an example - I am trying to find each employee and then all the employees that report to them BUT the whole way down the tree. For example KING will have an entry for everyone against his name as everyone either directly or indirectly reports to him. Then JONES, CLARK and BATE will each have entries with all the employees that either directly or indirectly report to them and so on. For example - the output would look something like this:

PARENT_ENAME EMPLOYEE_ENAME
------------ ----------------
KING (KING will have an entry of all employees)
...
JONES SCOTT (SCOTT reports to JONES)
JONES FORD (FORD reports to JONES)
JONES ADAMS (ADAMS reports to SCOTT who reports to JONES)
JONES SMITH (SMITH reports to FORD who reports to JONES)
etc.
Any help or suggestions on this would be greatly appreciated. I have tried using the CONNECT BY PRIOR functionality but without any success so far but I am sure that this may be where the solution lies.

Regards
bluezulu
Re: SQL CHALLENGE TO THE EXPERTS OUT THERE [message #184744 is a reply to message #184735] Thu, 27 July 2006 13:25 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8621
Registered: November 2002
Location: California, USA
Senior Member
Use connect_by_root. Here is an example in the online documentation that does exactly what you have described:

http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14200/queries003.htm#i2069380
Re: SQL CHALLENGE TO THE EXPERTS OUT THERE [message #184906 is a reply to message #184744] Fri, 28 July 2006 09:53 Go to previous messageGo to next message
bluezulu
Messages: 3
Registered: July 2006
Location: UK
Junior Member
Thank you Barbara - my appologies - I should have mentioned that I was using Oracle 9i. I did run this against 10g and it worked great however I need this for a 9i delivery.
Regards
bluezulu
Re: SQL CHALLENGE TO THE EXPERTS OUT THERE [message #184919 is a reply to message #184906] Fri, 28 July 2006 12:31 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8621
Registered: November 2002
Location: California, USA
Senior Member
scott@ORA92> SELECT banner FROM v$version
  2  /

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE	9.2.0.1.0	Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

scott@ORA92> COLUMN  parent_ename   FORMAT A15
scott@ORA92> COLUMN  employee_ename FORMAT A15
scott@ORA92> SELECT  SUBSTR (scbp, 1, INSTR (scbp, '/') - 1) AS parent_ename,
  2  	     employee_ename
  3  FROM    (SELECT  SUBSTR (SYS_CONNECT_BY_PATH (ename, '/'), 2) AS scbp,
  4  		      ename AS employee_ename,
  5  		      ROWNUM AS rn
  6  	      FROM    emp
  7  	      WHERE   LEVEL > 1
  8  	      CONNECT BY PRIOR empno = mgr)
  9  ORDER   BY rn DESC
 10  /

PARENT_ENAME    EMPLOYEE_ENAME
--------------- ---------------
KING            MILLER
KING            CLARK
KING            JAMES
KING            TURNER
KING            MARTIN
KING            WARD
KING            ALLEN
KING            BLAKE
KING            SMITH
KING            FORD
KING            ADAMS
KING            SCOTT
KING            JONES
CLARK           MILLER
BLAKE           JAMES
BLAKE           TURNER
BLAKE           MARTIN
BLAKE           WARD
BLAKE           ALLEN
JONES           SMITH
JONES           FORD
JONES           ADAMS
JONES           SCOTT
FORD            SMITH
SCOTT           ADAMS

25 rows selected.

scott@ORA92>


Re: SQL CHALLENGE TO THE EXPERTS OUT THERE [message #185236 is a reply to message #184735] Mon, 31 July 2006 12:24 Go to previous message
bluezulu
Messages: 3
Registered: July 2006
Location: UK
Junior Member
Thank you Barbara - much appreciated.
Previous Topic: How to insert only the time value in the date column
Next Topic: How to pass string having '&' in it to procedure IN parameter
Goto Forum:
  


Current Time: Fri Dec 02 19:04:09 CST 2016

Total time taken to generate the page: 0.10356 seconds