Re: tree structures in database tables?

Try this:
column lev format a15
select lpad(level,level*2) lev, region_name from regions start with region_group is null
connect by prior region_group = region_id;

Andy Ganse 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 Mon Feb 23 1998 - 00:00:00 CST