Re: sql question - hierarchy

"Lennart Jonsson" <lennart_at_kommunicera.umea.se> wrote in message news:6dae7e65.0303251205.2dd6dc6f_at_posting.google.com...
> "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

Tnx Lennart but unfortunatelly I'm fighting with Oracle SQL :-( and that kind of query is not supported in it.
But, tnx u anyway. Received on Wed Mar 26 2003 - 11:05:54 CET