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: Lennart Jonsson <lennart_at_kommunicera.umea.se>
Date: 25 Mar 2003 12:05:48 -0800
Message-ID: <6dae7e65.0303251205.2dd6dc6f@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 (

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

Original text of this message

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