Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: sql question - hierarchy

Re: sql question - hierarchy

From: Mikito Harakiri <mikharakiri_at_yahoo.com>
Date: 26 Mar 2003 12:36:28 -0800
Message-ID: <bdf69bdf.0303261236.82688ef@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

There is clearly a typo in your message: do you want a transitive closure of names or ids?

If you want transitive closure of ids you don't even have to mention the other table. Your result table, however, indicates that you probably want a transitive closure of names. Make the adjacency list of names as a first step:

select master.name MASTER_ORG_NAME, current.name ORG_ID_NAME from HIERARCHY h, ORGANIZATION master, ORGANIZATION current where h.org_id_parent = master.id
and h.org_id = current.id

You can use it as inlined view, named view, or as named refactoring clause (aka "with") in the following step. We'll refer to this view as AL_NAMES.

Next step, is building transitive closure like this:

select CONNECT_BY_PATH(ORG_ID_NAME, '/') from AL_NAMES
connect by MASTER_ORG_NAME = prior ORG_ID_NAME

Notice that first and last elements of the path are the names you are after. Extract them by applying a SUBSTR function. Received on Wed Mar 26 2003 - 14:36:28 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US