 | |
Hierarchical Subquery must omit nodes
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#21
 
| |||
| |||
|
Re: Hierarchical Subquery must omit nodes -
04-16-2008
, 05:43 PM
|
On Apr 16, 2:21 pm, "Shakespeare" <what... (AT) xs4all (DOT) nl> wrote: "fitzjarr... (AT) cox (DOT) net" <orat... (AT) msn (DOT) com> schreef in berichtnews:57056886-02e4-4188-9b29-b7ddf517bbb7 (AT) a70g2000hsh (DOT) googlegroups.com... On Apr 16, 12:16 pm, "Chris L." <diver... (AT) uol (DOT) 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
#22
| |||
| |||
|
|
fitzjarr... (AT) cox (DOT) net schrieb: On Apr 16, 2:21 pm, "Shakespeare" <what... (AT) xs4all (DOT) nl> wrote: "fitzjarr... (AT) cox (DOT) net" <orat... (AT) msn (DOT) com> schreef in berichtnews:57056886-02e4-4188-9b29-b7ddf517bbb7 (AT) a70g2000hsh (DOT) googlegroups.com... On Apr 16, 12:16 pm, "Chris L." <diver... (AT) uol (DOT) 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 |
"connect by" to it is a good idea.
Since I would work with employee_id to exclude, do you think it would
be best to filter out both employee_id and manager_id? Or is this a
non-issue?
What do you think of this...? sorry about the column name "last_name",
it would really contain ids.
Quote:
|
SQL> SELECT LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(last_name, '/') "Path" 2 FROM (select * 3 from employees 4 where not exists (select * 5 from excluded_nodes where last_name in (employee_id, manager_id) )) 6 START WITH last_name = 'Kochhar' 7 CONNECT BY PRIOR employee_id = manager_id; |
Chris
#23
| |||
| |||
|
|
fitzjarr... (AT) cox (DOT) net schrieb: On Apr 16, 2:21 pm, "Shakespeare" <what... (AT) xs4all (DOT) nl> wrote: "fitzjarr... (AT) cox (DOT) net" <orat... (AT) msn (DOT) com> schreef in berichtnews:57056886-02e4-4188-9b29-b7ddf517bbb7 (AT) a70g2000hsh (DOT) googlegroups.com... On Apr 16, 12:16 pm, "Chris L." <diver... (AT) uol (DOT) 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 |
"connect by" to it is a good idea.
Since I would work with employee_id to exclude, do you think it would
be best to filter out both employee_id and manager_id? Or is this a
non-issue?
What do you think of this...? sorry about the column name "last_name",
it would really contain ids.
Quote:
|
SQL> SELECT LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(last_name, '/') "Path" 2 FROM (select * 3 from employees 4 where not exists (select * 5 from excluded_nodes where last_name in (employee_id, manager_id) )) 6 START WITH last_name = 'Kochhar' 7 CONNECT BY PRIOR employee_id = manager_id; |
Chris
#24
| |||
| |||
|
|
fitzjarr... (AT) cox (DOT) net schrieb: On Apr 16, 2:21 pm, "Shakespeare" <what... (AT) xs4all (DOT) nl> wrote: "fitzjarr... (AT) cox (DOT) net" <orat... (AT) msn (DOT) com> schreef in berichtnews:57056886-02e4-4188-9b29-b7ddf517bbb7 (AT) a70g2000hsh (DOT) googlegroups.com... On Apr 16, 12:16 pm, "Chris L." <diver... (AT) uol (DOT) 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 |
"connect by" to it is a good idea.
Since I would work with employee_id to exclude, do you think it would
be best to filter out both employee_id and manager_id? Or is this a
non-issue?
What do you think of this...? sorry about the column name "last_name",
it would really contain ids.
Quote:
|
SQL> SELECT LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(last_name, '/') "Path" 2 FROM (select * 3 from employees 4 where not exists (select * 5 from excluded_nodes where last_name in (employee_id, manager_id) )) 6 START WITH last_name = 'Kochhar' 7 CONNECT BY PRIOR employee_id = manager_id; |
Chris
#25
| |||
| |||
|
|
fitzjarr... (AT) cox (DOT) net schrieb: On Apr 16, 2:21 pm, "Shakespeare" <what... (AT) xs4all (DOT) nl> wrote: "fitzjarr... (AT) cox (DOT) net" <orat... (AT) msn (DOT) com> schreef in berichtnews:57056886-02e4-4188-9b29-b7ddf517bbb7 (AT) a70g2000hsh (DOT) googlegroups.com... On Apr 16, 12:16 pm, "Chris L." <diver... (AT) uol (DOT) 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 |
"connect by" to it is a good idea.
Since I would work with employee_id to exclude, do you think it would
be best to filter out both employee_id and manager_id? Or is this a
non-issue?
What do you think of this...? sorry about the column name "last_name",
it would really contain ids.
Quote:
|
SQL> SELECT LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(last_name, '/') "Path" 2 FROM (select * 3 from employees 4 where not exists (select * 5 from excluded_nodes where last_name in (employee_id, manager_id) )) 6 START WITH last_name = 'Kochhar' 7 CONNECT BY PRIOR employee_id = manager_id; |
Chris
#26
| |||
| |||
|
|
On Apr 16, 6:43 pm, Urs Metzger <u... (AT) ursmetzger (DOT) de> wrote: fitzjarr... (AT) cox (DOT) net schrieb: On Apr 16, 2:21 pm, "Shakespeare" <what... (AT) xs4all (DOT) nl> wrote: "fitzjarr... (AT) cox (DOT) net" <orat... (AT) msn (DOT) com> schreef in berichtnews:57056886-02e4-4188-9b29-b7ddf517bbb7 (AT) a70g2000hsh (DOT) googlegroups.com... On Apr 16, 12:16 pm, "Chris L." <diver... (AT) uol (DOT) 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 Thanks Urs, working with a filtered subset and then applying the "connect by" to it is a good idea. Since I would work with employee_id to exclude, do you think it would be best to filter out both employee_id and manager_id? Or is this a non-issue? What do you think of this...? sorry about the column name "last_name", it would really contain ids. SQL> SELECT LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(last_name, '/') "Path" * *2 * * FROM (select * * *3 * * * * * * from employees * *4 * * * * * *where not exists (select * * *5 * * * * * * * * * * * * * * * ** * *from excluded_nodes where last_name in (employee_id, manager_id))) * *6 * * START WITH last_name = 'Kochhar' * *7 * * CONNECT BY PRIOR employee_id = manager_id; Appreciate it! Chris- Hide quoted text - - Show quoted text - |
the associated name and, in this example, you have no employee_id
column in the excluded_nodes table:
SQL> select *
2 from employees
3 where not exists (select *
4 from excluded_nodes where last_name in
(employee_id, manager_id) );
from excluded_nodes where last_name in (employee_id,
manager_id) )
*
ERROR at line 4:
ORA-01722: invalid number
The query supplied works as expected; if you're having trouble
'translating' it to your specific case then post your query and
someone here can help you with the rewrite.
David Fitzjarrell
#27
| |||
| |||
|
|
On Apr 16, 6:43 pm, Urs Metzger <u... (AT) ursmetzger (DOT) de> wrote: fitzjarr... (AT) cox (DOT) net schrieb: On Apr 16, 2:21 pm, "Shakespeare" <what... (AT) xs4all (DOT) nl> wrote: "fitzjarr... (AT) cox (DOT) net" <orat... (AT) msn (DOT) com> schreef in berichtnews:57056886-02e4-4188-9b29-b7ddf517bbb7 (AT) a70g2000hsh (DOT) googlegroups.com... On Apr 16, 12:16 pm, "Chris L." <diver... (AT) uol (DOT) 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 Thanks Urs, working with a filtered subset and then applying the "connect by" to it is a good idea. Since I would work with employee_id to exclude, do you think it would be best to filter out both employee_id and manager_id? Or is this a non-issue? What do you think of this...? sorry about the column name "last_name", it would really contain ids. SQL> SELECT LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(last_name, '/') "Path" * *2 * * FROM (select * * *3 * * * * * * from employees * *4 * * * * * *where not exists (select * * *5 * * * * * * * * * * * * * * * ** * *from excluded_nodes where last_name in (employee_id, manager_id))) * *6 * * START WITH last_name = 'Kochhar' * *7 * * CONNECT BY PRIOR employee_id = manager_id; Appreciate it! Chris- Hide quoted text - - Show quoted text - |
the associated name and, in this example, you have no employee_id
column in the excluded_nodes table:
SQL> select *
2 from employees
3 where not exists (select *
4 from excluded_nodes where last_name in
(employee_id, manager_id) );
from excluded_nodes where last_name in (employee_id,
manager_id) )
*
ERROR at line 4:
ORA-01722: invalid number
The query supplied works as expected; if you're having trouble
'translating' it to your specific case then post your query and
someone here can help you with the rewrite.
David Fitzjarrell
#28
| |||
| |||
|
|
On Apr 16, 6:43 pm, Urs Metzger <u... (AT) ursmetzger (DOT) de> wrote: fitzjarr... (AT) cox (DOT) net schrieb: On Apr 16, 2:21 pm, "Shakespeare" <what... (AT) xs4all (DOT) nl> wrote: "fitzjarr... (AT) cox (DOT) net" <orat... (AT) msn (DOT) com> schreef in berichtnews:57056886-02e4-4188-9b29-b7ddf517bbb7 (AT) a70g2000hsh (DOT) googlegroups.com... On Apr 16, 12:16 pm, "Chris L." <diver... (AT) uol (DOT) 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 Thanks Urs, working with a filtered subset and then applying the "connect by" to it is a good idea. Since I would work with employee_id to exclude, do you think it would be best to filter out both employee_id and manager_id? Or is this a non-issue? What do you think of this...? sorry about the column name "last_name", it would really contain ids. SQL> SELECT LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(last_name, '/') "Path" * *2 * * FROM (select * * *3 * * * * * * from employees * *4 * * * * * *where not exists (select * * *5 * * * * * * * * * * * * * * * ** * *from excluded_nodes where last_name in (employee_id, manager_id))) * *6 * * START WITH last_name = 'Kochhar' * *7 * * CONNECT BY PRIOR employee_id = manager_id; Appreciate it! Chris- Hide quoted text - - Show quoted text - |
the associated name and, in this example, you have no employee_id
column in the excluded_nodes table:
SQL> select *
2 from employees
3 where not exists (select *
4 from excluded_nodes where last_name in
(employee_id, manager_id) );
from excluded_nodes where last_name in (employee_id,
manager_id) )
*
ERROR at line 4:
ORA-01722: invalid number
The query supplied works as expected; if you're having trouble
'translating' it to your specific case then post your query and
someone here can help you with the rewrite.
David Fitzjarrell
#29
| |||
| |||
|
|
On Apr 16, 6:43 pm, Urs Metzger <u... (AT) ursmetzger (DOT) de> wrote: fitzjarr... (AT) cox (DOT) net schrieb: On Apr 16, 2:21 pm, "Shakespeare" <what... (AT) xs4all (DOT) nl> wrote: "fitzjarr... (AT) cox (DOT) net" <orat... (AT) msn (DOT) com> schreef in berichtnews:57056886-02e4-4188-9b29-b7ddf517bbb7 (AT) a70g2000hsh (DOT) googlegroups.com... On Apr 16, 12:16 pm, "Chris L." <diver... (AT) uol (DOT) 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 Thanks Urs, working with a filtered subset and then applying the "connect by" to it is a good idea. Since I would work with employee_id to exclude, do you think it would be best to filter out both employee_id and manager_id? Or is this a non-issue? What do you think of this...? sorry about the column name "last_name", it would really contain ids. SQL> SELECT LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(last_name, '/') "Path" * *2 * * FROM (select * * *3 * * * * * * from employees * *4 * * * * * *where not exists (select * * *5 * * * * * * * * * * * * * * * ** * *from excluded_nodes where last_name in (employee_id, manager_id))) * *6 * * START WITH last_name = 'Kochhar' * *7 * * CONNECT BY PRIOR employee_id = manager_id; Appreciate it! Chris- Hide quoted text - - Show quoted text - |
the associated name and, in this example, you have no employee_id
column in the excluded_nodes table:
SQL> select *
2 from employees
3 where not exists (select *
4 from excluded_nodes where last_name in
(employee_id, manager_id) );
from excluded_nodes where last_name in (employee_id,
manager_id) )
*
ERROR at line 4:
ORA-01722: invalid number
The query supplied works as expected; if you're having trouble
'translating' it to your specific case then post your query and
someone here can help you with the rewrite.
David Fitzjarrell
#30
| |||
| |||
|
|
On Apr 17, 10:09*am, "Chris L." <diver... (AT) uol (DOT) com.ar> wrote: On Apr 16, 6:43 pm, Urs Metzger <u... (AT) ursmetzger (DOT) de> wrote: fitzjarr... (AT) cox (DOT) net schrieb: On Apr 16, 2:21 pm, "Shakespeare" <what... (AT) xs4all (DOT) nl> wrote: "fitzjarr... (AT) cox (DOT) net" <orat... (AT) msn (DOT) com> schreef in berichtnews:57056886-02e4-4188-9b29-b7ddf517bbb7 (AT) a70g2000hsh (DOT) googlegroups.com... On Apr 16, 12:16 pm, "Chris L." <diver... (AT) uol (DOT) 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 Thanks Urs, working with a filtered subset and then applying the "connect by" to it is a good idea. Since I would work with employee_id to exclude, do you think it would be best to filter out both employee_id and manager_id? Or is this a non-issue? What do you think of this...? sorry about the column name "last_name", it would really contain ids. SQL> SELECT LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(last_name, '/') "Path" * *2 * * FROM (select * * *3 * * * * * * from employees * *4 * * * * * *where not exists (select * * *5 * * * * * * * * * * * * * * * * * * *from excluded_nodes where last_name in (employee_id, manager_id) )) * *6 * * START WITH last_name = 'Kochhar' * *7 * * CONNECT BY PRIOR employee_id = manager_id; Appreciate it! Chris- Hide quoted text - - Show quoted text - Your modification won't work; *the id values won't ever match up to the associated name and, in this example, you have no employee_id column in the excluded_nodes table: SQL> select * * 2 *from employees * 3 *where not exists (select * * 4 * * * * * * * * * *from excluded_nodes where last_name in (employee_id, manager_id) ); * * * * * * * * * from excluded_nodes where last_name in(employee_id, manager_id) ) * * * * * * * * * * * * * * * * * * * * * * * ERROR at line 4: ORA-01722: invalid number The query supplied works as expected; if you're having trouble 'translating' it to your specific case then post *your query and someone here can help you with the rewrite. David Fitzjarrell- Hide quoted text - - Show quoted text - |
SQL> SELECT LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(last_name, '/')
"Path"
2 FROM (select *
3 from employees e
4 where not exists (select *
5 from excluded_nodes where employee_id in
(e.employee_id, e.manager_id) ))
6 START WITH last_name = 'Kochhar'
7 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.
I would use the example as posted, and try to modify your original
query in a similar manner.
David Fitzjarrell
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.