Re: Hierarchical Subquery must omit nodes

From: Urs Metzger <urs_at_ursmetzger.de>
Date: Wed, 16 Apr 2008 20:45:43 +0200
Message-ID: <fu5hdd$8to$1@online.de>

fitzjarrell_at_cox.net schrieb:

> 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

David, which Oracle version are you using?

Urs Metzger Received on Wed Apr 16 2008 - 13:45:43 CDT