Re: tough RECURSIVE QUERY, How?

From: Peter Schneider <pschneider.ctj_at_metronet.de>
Date: 1998/01/14
Message-ID: <34bd3640.5841880@pop-news.metronet.de>#1/1

On 14 Jan 1998 15:56:54 GMT, Nnoor_at_cris.com (NNOOR) wrote:

>
>Hi,
>Following is the scenerio:
>
>An office can have one or more sub-offices. Each of these sub-offices
>can have one or more sub-sub-offices...so on. I have implemented this
>in a single table.
>e.g.
>PARENT_OFF_ID OFFICE_ID
>20 21
>20 22
>21 23
>24 25
>23 26
>23 27 etc etc
>
>For a given office_id, the query should return either just the office_id
>passes as the parameter (if it happens to have NO sub offices) or ALL of
>the LOWEST level offices.
>
>e.g. if passed, 25, it should return 25.
>if passed 20, it should return 22,26,27 (notice NOT 21 and 23)
>if passed 21, it should return 26,27
>if passed 23, it shoudl return 26,27
>
>Can anyone help me with this? Can it be done just thru SQL?
>
>Thanks and Regards,
>Nasir (nnoor_at_cris.com)

Hi Nasir,

this is quite easy, have a look at the following SQL:

SQL> create table offices

  2  (office_id number(12),
  3   parent_office_id number(12),
  4   office_code varchar2(10) not null,

  5 constraint office_pk primary key(office_id),   6 constraint office_office_fk

  7     foreign key (parent_office_id)
  8     references offices);

Table created.

SQL> desc offices

 Name                            Null?    Type
 ------------------------------- -------- ----
 OFFICE_ID                       NOT NULL NUMBER(12)
 PARENT_OFFICE_ID                         NUMBER(12)
 OFFICE_CODE                     NOT NULL VARCHAR2(10)

SQL> insert into offices
  2 values
  3 (20, null, 'O-20');

1 row created.

[...inserted some more data...]

1 row created.

SQL> commit;

Commit complete.

SQL> select lpad(office_id, level+1, ' ') structure,   2 parent_office_id, office_code, level   3 from offices
  4 connect by prior office_id = parent_office_id   5 start with parent_office_id is null;

STRUCTURE PARENT_OFFICE_ID OFFICE_COD LEVEL

-------------------- ---------------- ---------- ---------
20                                    O-20               1
 21                                20 O-21               2
  23                               21 O-23               3
   26                              23 O-26               4
   27                              23 O-27               4
 22                                20 O-22               2
24                                    O-24               1
 25                                24 O-25               2

8 rows selected.

SQL> select o1.office_id, o1.parent_office_id, o1.office_code   2 from offices o1
  3 where not exists
  4 (select 1 from offices o2
  5 where o1.office_id = o2.parent_office_id)   6 connect by prior o1.office_id = o1.parent_office_id   7* start with o1.office_id = &office_id SQL> /
Enter value for office_id: 25
old 7: start with o1.office_id = &office_id new 7: start with o1.office_id = 25

OFFICE_ID PARENT_OFFICE_ID OFFICE_COD

--------- ---------------- ----------
       25               24 O-25

SQL> /
Enter value for office_id: 20
old 7: start with o1.office_id = &office_id new 7: start with o1.office_id = 20

OFFICE_ID PARENT_OFFICE_ID OFFICE_COD

--------- ---------------- ----------
       26               23 O-26
       27               23 O-27
       22               20 O-22

SQL> /
Enter value for office_id: 21
old 7: start with o1.office_id = &office_id new 7: start with o1.office_id = 21

OFFICE_ID PARENT_OFFICE_ID OFFICE_COD

--------- ---------------- ----------
       26               23 O-26
       27               23 O-27

SQL> /
Enter value for office_id: 23
old 7: start with o1.office_id = &office_id new 7: start with o1.office_id = 23

OFFICE_ID PARENT_OFFICE_ID OFFICE_COD

--------- ---------------- ----------
       26               23 O-26
       27               23 O-27

SQL> Hope this helps,
Peter

-- 
Peter Schneider
pschneider.ctj_at_metronet.de

Received on Wed Jan 14 1998 - 00:00:00 CST