Re: Hierarchical Subquery must omit nodes

From: Urs Metzger <urs_at_ursmetzger.de>
Date: Wed, 16 Apr 2008 23:43:36 +0200
Message-ID: <fu5rqu$lrt$1@online.de>

fitzjarrell_at_cox.net schrieb:

> On Apr 16, 2:21 pm, "Shakespeare" <what..._at_xs4all.nl> wrote:

>> "fitzjarr..._at_cox.net" <orat..._at_msn.com> schreef in berichtnews: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- Hide quoted text -
>>
>> - Show quoted text -

> 
> A valid concern.  I must admit this was a quick and dirty attempt.
> 
> And this is with 10.2.0.3.
> 
> 
> David Fitzjarrell

SQL> create table excluded_nodes(last_name varchar2(25));

Tabelle wurde erstellt.

SQL> insert into excluded_nodes values('Greenberg');

1 Zeile wurde erstellt.

SQL> commit;

SQL> SELECT LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(last_name, '/') "Path"

   2     FROM (select *
   3             from employees
   4            where last_name not in (select last_name
   5                                      from excluded_nodes))
   6     START WITH last_name = 'Kochhar'
   7     CONNECT BY PRIOR employee_id = manager_id;

Path

---

  /Kochhar

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

hth,
Urs Metzger Received on Wed Apr 16 2008 - 16:43:36 CDT