Re: Hierarchical Subquery must omit nodes

From: <fitzjarrell_at_cox.net>
Date: Wed, 16 Apr 2008 11:17:57 -0700 (PDT)
Message-ID: <57056886-02e4-4188-9b29-b7ddf517bbb7@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 Received on Wed Apr 16 2008 - 13:17:57 CDT