| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: tree structures in database tables?
I set up the example table and rows from your posting and ran the following SQL*Plus script which uses the START WITH and CONNECT BY PRIOR verbs.
column org_chart format a40 heading 'Region Structure' column region_id heading 'Region|Id' column parent_region heading 'Region|Group'
SELECT LPAD(' ',2*(LEVEL-1)) || r.region_name org_chart,
r.region_id region_id,
r.region_group parent_region
FROM regions r
which resulted in the following output :-
Regi Regi
Region Structure Id Grou
---------------------------------------- ---- ----
State of Washington WAST
Puget Sound Area PUGS WAST
King County KING PUGS
City of Seattle SEAT KING
City of Bellevue BELL KING
State of Oregon ORST
Some County in OR SOMC ORST
7 rows selected.
Hope this is of some help.
Andy Ganse <andy_ganse_at_psmfc.org> wrote:
>Hi all -
>I'm looking for a SQL statement or PL/SQL procedure to output the ordered
>structure of a data tree held in my Oracle database table. Unfortunately
>it didn't end up as easy as I first thought and I'm looking for a hand.
>
>Here's what I've got:
>I've a table that sets up a kind of tree, with the following columns:
> table REGIONS
> ---------------------------
> region_id CHAR(4)
> region_name VARCHAR2(20)
> region_group CHAR(4)
>
>The regions are hierarchical; for example I might have City of Seattle
>within King County within Puget Sound Area within State of WA, etc. So
>some table entries might look like this:
> REGION_ID REGION_NAME REGION_GROUP
> --------- ---------------- ------------
> SEAT City of Seattle KING
> BELL City of Bellevue KING
> KING King County PUGS
> PUGS Puget Sound Area WAST
> WAST State of Washington null
> ORST State of Oregon null
> SOMC Some County in OR ORST
>(My tree-depth is not constant, so I don't know at any given time how
>many generations I have - and siblings and children are added in
>wherever's necessary by adding a new record to the bottom of the list.)
>
>What I first tried to list this out was something along the lines of:
> SELECT a.region_name, b.region_name, c.region_name
> FROM regions a, regions b, regions c
> WHERE a.region_group = NULL
> AND b.region_group = a.region_id
> AND c.region_group = b.region_id
> ORDER BY a.region_name, b.region_name, c.region_name
>
>But the problem is, as I mentioned above, my tree-depth doesn't stay
>constant, while this statement requires a specific depth. Also, my
>tree-depth is also generally much deeper than the above example, making
>for a really icky SQL statement anyways. And that's where I'm stuck.
>
>A guy in another newsgroup said he remembered some kind of Oracle
>extensions to handle tree-structured data in tables, but he didn't
>remember what they were and I can't seem to find anything about it in my
>manuals. Any of y'all familiar with that? Or have a good way through my
>problem?
>
>Much thanks in advance!
>Cheers,
>-Andy Ganse
Received on Tue Feb 24 1998 - 00:00:00 CST
 |
 |