Re: Querying Hierarchical Table

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 - 12:07:23 CST