Re: Hierarchical quera

From: Ranga Chakravarthi <ranga_at_removethis.cfl.rr.com>
Date: Tue, 04 Jun 2002 01:32:18 GMT
Message-ID: <CMUK8.71392$ec1.1279365_at_twister.tampabay.rr.com>

Hi,

You can accomplish this using a view:

SQL> select * from org_units;

O         ID NAME                           O     OUT_ID
- ---------- ------------------------------ - ----------
A        100 Top Level Organization
B       1001 B (1001) Managed by A          A        100
C       1002 C (1002) Managed by A          A        100
D       2001 D (2001) Managed by B          B       1001
E       2002 E (2002) Managed by C          C       1002
F       3001 F (3001) Managed by D          D       2001

SQL> select id, org_type, lpad(' ', 3 * level - 3) || name org_chart   2 from
  3 (

  4     select id || org_type pkey,
  5            out_id || out_org_type fkey,
  6            id,
  7            org_type,
  8            out_id,
  9            out_org_type,
 10            name
 11     from org_units

 12 )
 13 connect by prior pkey = fkey
 14 start with fkey is null
 15 /

        ID O ORG_CHART

---------- - ----------------------------------------
       100 A Top Level Organization
      1001 B    B (1001) Managed by A
      2001 D       D (2001) Managed by B
      3001 F          F (3001) Managed by D
      1002 C    C (1002) Managed by A
      2002 E       E (2002) Managed by C

"El - Fatih" <zivjeli_at_veselili.se> wrote in message news:ad269g$f4o6_at_ns4.bih.net.ba...
> Hi
>
> I have a problem with displaying the organisational structure from table
> org_units which have the following structure:
>
> Name Null? Type
> ------------------------------- -------- ----
> ORG_TYPE NOT NULL VARCHAR2(1)
> ID NOT NULL NUMBER(38)
> NAME NOT NULL VARCHAR2(100)
> OUT_ORG_TYPE VARCHAR2(1)
> OUT_ID NUMBER(38)
>
> Primary key of this table is OUT_PK and it is composed of two columns:
> ORG_TYPE and ID.
> This table has foreign key to itself composed of following two columns
> OUT_ORG_TYPE and OUT_ID.
> Columns OUT_ORG_TYPE and OUT_ID points to superior organisational units.
> Can you help me, please, to make appropriate query.
> This query does not work.
>
> select lpad(' ',3*level-3)||name from org_units
> connect by prior org_type=out_org_type and id=out_id
> start with out_org_typeis null and out_id is null
>
> Thanx
>
>
>
>
Received on Tue Jun 04 2002 - 03:32:18 CEST