Hierarchical Subquery must omit nodes

#11

"fitzjarrell (AT) cox (DOT) net" <oratune (AT) msn (DOT) com> schreef in bericht
news: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:

Quote:

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

#12

"fitzjarrell (AT) cox (DOT) net" <oratune (AT) msn (DOT) com> schreef in bericht
news: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:

Quote:

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

#13

"fitzjarrell (AT) cox (DOT) net" <oratune (AT) msn (DOT) com> schreef in bericht
news: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:

Quote:

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

#14

On Apr 16, 2:21*pm, "Shakespeare" <what... (AT) xs4all (DOT) nl> wrote:

Quote:

"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

#15

On Apr 16, 2:21*pm, "Shakespeare" <what... (AT) xs4all (DOT) nl> wrote:

Quote:

"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

#16

On Apr 16, 2:21*pm, "Shakespeare" <what... (AT) xs4all (DOT) nl> wrote:

Quote:

"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

#17

On Apr 16, 2:21*pm, "Shakespeare" <what... (AT) xs4all (DOT) nl> wrote:

Quote:

"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

#18

fitzjarrell (AT) cox (DOT) net schrieb:

Quote:

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

#19

fitzjarrell (AT) cox (DOT) net schrieb:

Quote:

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

#20

fitzjarrell (AT) cox (DOT) net schrieb:

Quote:

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