Re: sql question - hierarchy

"LokalST" <stbest001_at_hotmail.com> wrote in message news:<b5pqba$eq4$1_at_sunce.iskon.hr>...
> I have two tables: ORGANIZATION and HIERARCHY.
>
> -- creating table organization and inserting values
> create table ORGANIZATION( ID NUMBER not null, NAME VARCHAR2(10))
> insert into ORGANIZATION (ID, NAME) values (1, 'A1');
> insert into ORGANIZATION (ID, NAME) values (2, 'B1');
> insert into ORGANIZATION (ID, NAME) values (3, 'B2');
> insert into ORGANIZATION (ID, NAME) values (4, 'C1');
> insert into ORGANIZATION (ID, NAME) values (5, 'C2');
> commit;
> --
> -- creating table hierarchy and inserting values
> create table HIERARCHY( ORG_ID NUMBER not null, ORG_ID_PARENT
> NUMBER not null)
> insert into HIERARCHY (ORG_ID, ORG_ID_PARENT) values (2, 1);
> insert into HIERARCHY (ORG_ID, ORG_ID_PARENT) values (3, 1);
> insert into HIERARCHY (ORG_ID, ORG_ID_PARENT) values (4, 2);
> insert into HIERARCHY (ORG_ID, ORG_ID_PARENT) values (5, 2);
> commit;
> --
> The problem is how to get the result with two columns that would look like
> this:
> MASTER_ORG_ID, ORG_ID_CURRENT
> A1 A1
> A1 B1
> A1 B2
> A1 C1
> A1 C2
> B1 B1
> B1 C1
> B1 C2
> B2 B2
> C1 C1
> C2 C2
>
> I tried with CONNECT BY clause but I couldn't get the desired result.
> Is it possible to write an SQL query returning the result described above?
> Thanks

I'm not familiar with Oracle and connect by, but with db2's recursive construct you can do it prox as:

with rec (x, y) as (

• root node select org_id_parent, org_id_parent from hierarchy h where not exists ( select 1 from hierarchy where org_id = h.org_id_parent ) union all
• self referencing nodes, i.e. A1, A1 select org_id, org_id from hierarchy union all
• ancestor relation select x, org_id_parent from rec r, hierarchy h where r.y = h.org_id ) select distinct o1.name, o2.name from organization o1, organization o2, rec r where r.y = o1.id and r.x = o2.id order by 1,2

NAME NAME
---------- ----------
SQL0347W The recursive common table expression "JON.REC" may contain an
infinite loop. SQLSTATE=01605

A1         A1        
A1         B1        
A1         B2        
A1         C1        
A1         C2        
B1         B1        
B1         C1        
B1         C2        
B2         B2        
C1         C1        
C2         C2        

  11 record(s) selected with 1 warning messages printed.

HTH
/Lennart Received on Tue Mar 25 2003 - 14:05:48 CST