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>
>> 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
David, which Oracle version are you using?
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