Re: Hierarchical Subquery must omit nodes

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Wed, 16 Apr 2008 21:21:32 +0200
Message-ID: <480651b0$0$14361$e4fe514c@news.xs4all.nl>

"fitzjarrell_at_cox.net" <oratune_at_msn.com> schreef in bericht news:57056886-02e4-4188-9b29-b7ddf517bbb7_at_a70g2000hsh.googlegroups.com... On Apr 16, 12:16 pm, "Chris L." <diver..._at_uol.com.ar> wrote:
> 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
> fromhttp://download.oracle.com/docs/cd/B10501_01/server.920/a96540/functi...
>
> 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/Whalen
> /Kochhar/Mavris
> /Kochhar/Baer
> /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

Why wouldn't this work:

• Your original example SQL> SELECT LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(last_name, '/') "Path" 2 FROM employees 3 START WITH last_name = 'Kochhar' 4 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/Whalen
   /Kochhar/Mavris

   /Kochhar/Baer
   /Kochhar/Higgins
     /Kochhar/Higgins/Gietz

12 rows selected.

• 'Filtered' example SQL> select "Path" 2 from 3 (SELECT LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(last_name, '/') "Path" 4 FROM employees 5 START WITH last_name = 'Kochhar' 6 CONNECT BY PRIOR employee_id = manager_id) 7 where instr("Path", 'Greenberg') = 0;

Path

---

 /Kochhar

   /Kochhar/Whalen
   /Kochhar/Mavris
   /Kochhar/Baer
   /Kochhar/Higgins
     /Kochhar/Higgins/Gietz

6 rows selected.

SQL> David Fitzjarrell

---

This will go wrong if you want to exclude 'Green' but include 'Greenberg'

Shakespeare Received on Wed Apr 16 2008 - 14:21:32 CDT