Re: Join on hierarchy query using the root element possible?

From: Roel Schreurs <schreurs_roel_at_hotmail.com>
Date: 6 Oct 2004 06:07:21 -0700
Message-ID: <321ebdef.0410060507.6de5f5b9@posting.google.com>

I found a solution to my question that works fine, but may not give the best performance. Since this query involves only configutation data and no production data, that is not a big problem.

The solution incorporates a FUNCTION that will receive the current node and it's level and it searches parents of the current node for as many levels as neccesary. The parent that is found will be the root node of the subtree.

The function is defined as follows:
FUNCTION PERMISSIONS_SUB_TREE_ROOT
( THE_ID IN NUMBER
, THE_LEVEL IN NUMBER

)
RETURN NUMBER
IS

    SUB_TREE_ROOT NUMBER(10);
BEGIN

	SELECT ID
	  INTO SUB_TREE_ROOT
	  FROM HIERARCHY
     WHERE LEVEL = THE_LEVEL
	 -- Connect 'upwards', i.e. find the parent
   CONNECT BY PRIOR PARENT = ID
	 START WITH ID = THE_ID;

    RETURN SUB_TREE_ROOT;
END; And the query as follows:

SELECT ID, NAME, USERNAME
FROM
(
SELECT ID, PARENT, NAME, PERMISSIONS_SUB_TREE_ROOT(ID, LEVEL) AS ROOT FROM HIERARCHY
CONNECT BY PRIOR ID = PARENT
) HIERARCHY
, PERMISSIONS

WHERE ROOT = HIERARCHY_ID; The result is like this:

ID	NAME	USERNAME
1	ROOT	U1
2	A	U1
3	AA	U1
4	B	U1
2	A	U2
3	AA	U2

Just what I needed. Received on Wed Oct 06 2004 - 08:07:21 CDT