Re: Recursive results of a n-level hierarchy

From: Michael O'Shea <michael.oshea_at_tessella.com>
Date: 7 Jan 2005 10:55:55 -0800
Message-ID: <1105124155.720193.280980@f14g2000cwb.googlegroups.com>

Hi, try this. The inline trace demonstrates how to create your table structure & data prior to performing a hierarchical query giving the required 'flat' format.

Mike

TESSELLA Michael.OShea_at_tessella.com

__/__/__/  Tessella Support Services plc
__/__/__/  3 Vineyard Chambers, ABINGDON, OX14 3PX, England
__/__/__/  Tel: (44)(0)1235-555511  Fax: (44)(0)1235-553301

www.tessella.com Registered in England No. 1466429

SQL>
SQL>
SQL> CREATE TABLE employee(rep_id NUMBER,mgr_id NUMBER);

Table created.

SQL> INSERT INTO employee(rep_id,mgr_id) 2 VALUES(1,NULL); 1 row created.

SQL> INSERT INTO employee(rep_id,mgr_id) 2 VALUES(2,1); 1 row created.

SQL> INSERT INTO employee(rep_id,mgr_id) 2 VALUES(3,1); 1 row created.

SQL> INSERT INTO employee(rep_id,mgr_id) 2 VALUES(4,3); 1 row created.

SQL> INSERT INTO employee(rep_id,mgr_id) 2 VALUES(5,3); 1 row created.

SQL> INSERT INTO employee(rep_id,mgr_id) 2 VALUES(6,4); 1 row created.

SQL> INSERT INTO employee(rep_id,mgr_id) 2 VALUES(7,5); 1 row created.

SQL>
SQL>
SQL> CREATE TABLE account(acct_id CHAR(1),acct_rep_id NUMBER);

Table created.

SQL> INSERT INTO account(acct_id,acct_rep_id) 2 VALUES('A',2); 1 row created.

SQL> INSERT INTO account(acct_id,acct_rep_id) 2 VALUES('B',3); 1 row created.

SQL> INSERT INTO account(acct_id,acct_rep_id) 2 VALUES('C',4); 1 row created.

SQL> INSERT INTO account(acct_id,acct_rep_id) 2 VALUES('D',5); 1 row created.

SQL> INSERT INTO account(acct_id,acct_rep_id) 2 VALUES('E',6); 1 row created.

SQL> INSERT INTO account(acct_id,acct_rep_id) 2 VALUES('F',7); 1 row created.

SQL>
SQL> SELECT e1.rep_id AS rep_or_mgr_id,
2 a1.acct_id AS acct_id
3 FROM account a1,
4 employee e1
5 WHERE a1.acct_rep_id IN

6     (
7       SELECT rep_id
8        FROM employee
9         WHERE NOT 0=(
10                       SELECT COUNT(*)
11                        FROM account
12                         WHERE rep_id=a1.acct_rep_id
13                     )
14          CONNECT BY PRIOR rep_id=mgr_id
15           START WITH rep_id=e1.rep_id
16     )
17      ORDER BY e1.rep_id ASC,
18               a1.ACCT_ID ASC;

REP_OR_MGR_ID A
------------- -
1 A
1 B
1 C
1 D
1 E
1 F
2 A
3 B
3 C
3 D
3 E

REP_OR_MGR_ID A
------------- -
3 F
4 C
4 E
5 D
5 F
6 E
7 F

18 rows selected.

SQL>
SQL> SELECT *
2 FROM V$VERSION; BANNER

---

Personal Oracle Database 10g Release 10.1.0.2.0 - Production

PL/SQL Release 10.1.0.2.0 - Production
CORE 10.1.0.2.0 Production
TNS for 32-bit Windows: Version 10.1.0.2.0 - Production NLSRTL Version 10.1.0.2.0 - Production

SQL> SPOOL OFF bikeratbrown wrote:
> I need to send a flat representation of our Acct Rep Team and
> Management groups to an external app. This data should be structed
in
> a way that would allow access to the owned accts in a reporting
> environment to all Reps and their Managers. In other words I need
any
> level of manager to have rights to all accounts they own and to
> accounts where a Rep anywhere below them is that account's Rep.
>
> For example from these two given tables:
> Table: employee
> REP_ID MGR_ID
> 1 null
> 2 1
> 3 1
> 4 3
> 5 3
> 6 4
> 7 5
>
> Table: account
> ACCT_ID ACCT_REP_ID
> A 2
> B 3
> C 4
> D 5
> E 6
> F 7
>
> How would I get this result:
> REP_OR_MGR_ID ACCT_ID
> 1 A
> 1 B
> 1 C
> 1 D
> 1 E
> 1 F
> 2 A
> 3 B
> 3 C
> 3 D
> 4 C
> 4 E
> 5 D
> 5 F
> 6 E
> 7 F
>
> I thought connect by/start with was my solution but don't see how I
can
> get the recursive results set desired. Any help is appreciated!
Received on Fri Jan 07 2005 - 12:55:55 CST