Hierarchical Subquery must omit nodes
Date: Wed, 16 Apr 2008 10:16:42 -0700 (PDT)
Message-ID: <00580b29-0d16-4dcb-9087-2e5e47b9c008@l64g2000hse.googlegroups.com>
Hi all,
I thought this would be easy but it's turning out to be pretty difficult.
I need a hierarchical query to omit nodes present in a EXCLUDED_NODES
table.
Omitted nodes' children should be omitted too.
See this example taken from http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/functions121a.htm#88362
SELECT LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(last_name, '/') "Path"
FROM employees
START WITH last_name = 'Kochhar'
CONNECT BY PRIOR employee_id = manager_id;
Path
/Kochhar
/Kochhar/Greenberg
/Kochhar/Greenberg/Faviet /Kochhar/Greenberg/Chen /Kochhar/Greenberg/Sciarra /Kochhar/Greenberg/Urman /Kochhar/Greenberg/Popp/Kochhar/Baer
/Kochhar/Whalen
/Kochhar/Mavris
/Kochhar/Higgins
/Kochhar/Higgins/Gietz
If I have Greenberg in EXCLUDED_NODES then output should be
Path
/Kochhar
/Kochhar/Whalen
/Kochhar/Mavris
/Kochhar/Baer
/Kochhar/Higgins
/Kochhar/Higgins/Gietz
Excluded node can be in any depth of the hierarchy (i.e. it could be Gietz and then Gietz and all of its dependent sub-tree should be pruned)
I've tried filtering the START WITH and works great but doesn't help when the excluded node is somewhere deep in the hierarchy.
I've tried filtering the CONNECT BY but it cannot contain subqueries (manual states this).
I've tried using the SYS_CONNECT_BY_PATH to parse it and see if it contains an excluded node but I got an Ora-00600 error.
ORA-00600: internal error code, arguments: [rworupo.2], [26670], [1], [], [], [], [], []
Oracle version is 9i
Thanks in advance!
Chris
Received on Wed Apr 16 2008 - 12:16:42 CDT