Re: Querying Hierarchical Table

From: Martin Haltmayer <Martin.Haltmayer_at_d2mail.de>
Date: Thu, 20 Dec 2001 17:28:14 +0100
Message-ID: <3C22119E.AAB0C69@d2mail.de>

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
Received on Thu Dec 20 2001 - 10:28:14 CST