Re: Querying Hierarchical Table

Hi Sybrand,

I *am* aware of the connect by feature.

However, there is no way to get the complete path in the tree from the current leaf up to the root. This is exactly what the original poster was asking for. Or do you know a way to achieve that by connect by? If so I am more than willing to learn that way. If not my suggestion is at least *a* solution.

Regards,

Martin

Sybrand Bakker wrote:
>
> On Thu, 20 Dec 2001 17:28:14 +0100, Martin Haltmayer
> <Martin.Haltmayer_at_d2mail.de> wrote:
>
> >You need a recursive function that does the trick for you. To show the principle
> >you can do the following:
> >
> >---------------------------------------------------------------
> >create or replace package hier_pack is
> >subtype restype is varchar2 (4000);
> >function complete_path (i_locality_id in hier.locality_id%type) return
> >hier_pack.restype parallel_enable;
> >pragma restrict_references (complete_path, rnps, wnps, wnds);
> >end hier_pack;
> >/
> >
> >create or replace package body hier_pack is
> >subtype restype is varchar2 (4000);
> >function complete_path (i_locality_id in hier.locality_id%type) return
> >hier_pack.restype is
> > l_res hier_pack.restype:= '';
> >begin
> > for r in (
> > select h.locality_parent
> > , h.locality_desc
> > from hier h
> > where 1 = 1
> > and i_locality_id = h.locality_id
> > ) loop
> > l_res:= r.locality_desc || ';' || hier_pack.complete_path (r.locality_parent);
> > end loop;
> > return l_res;
> >end complete_path;
> >end hier_pack;
> >/
> >show errors
> >
> >select lpad (';', level - 1, ';')
> > || h.locality_desc
> > , hier_pack.complete_path (h.locality_id)
> >from hier h
> >start with h.locality_parent is null
> >connect by prior locality_id = locality_parent
> >/
> >---------------------------------------------------------------
> >
> >Regards,
> >
> >Martin
> >
> >
> >
> >
> >sweidanz_at_yahoo.com wrote:
> >>
> >> I am trying to think of an efficient way to write a query on a
> >> hierarchical table.
> >> The table is as follows:
> >>
> >> locality_id locality_parent locality_desc
> >> 1 0 NSW
> >> 2 1 Sydney
> >> 3 2 Hunter Street
> >>
> >> I need a query to display the above three levels in one line. 3 levels is
> >> the maximum and most likely to be.
> >>
> >> I thought of the query as follows:
> >>
> >> SELECT loc3.locality_desc|| ' '|| loc2.locality_desc || ' '||
> >> loc1.locality_desc AS loc_address
> >> FROM locality loc1,
> >> locality loc2,
> >> locality loc3
> >> WHERE
> >> loc3.locality_id = 3 AND
> >> loc3.locality_parent = loc2.locality_id AND
> >> loc2.locality_parent = loc1.locality_id AND
> >>
> >> Does anyone have any other more efficient way of writing the query?
> >>
> >> Thanks,
> >> ZS
>
> Both of you should be aware of the CONNECT BY clause of Oracle before
> resorting to tailor made table specific functions
>
> Regards
>
> Sybrand Bakker, Senior Oracle DBA
>
> To reply remove -verwijderdit from my e-mail address
Received on Thu Dec 20 2001 - 18:45:12 CST