On Apr 16, 12:16=A0pm, "Chris L." <diver...@[EMAIL PROTECTED]
> 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/se=
rver.920/a96540/functi...
>
> SELECT LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(last_name, '/')
> "Path"
> =A0 =A0FROM employees
> =A0 =A0START WITH last_name =3D 'Kochhar'
> =A0 =A0CONNECT BY PRIOR employee_id =3D manager_id;
>
> Path
> ---------------------------------------------------------------
> =A0/Kochhar
> =A0 =A0/Kochhar/Greenberg
> =A0 =A0 =A0/Kochhar/Greenberg/Faviet
> =A0 =A0 =A0/Kochhar/Greenberg/Chen
> =A0 =A0 =A0/Kochhar/Greenberg/Sciarra
> =A0 =A0 =A0/Kochhar/Greenberg/Urman
> =A0 =A0 =A0/Kochhar/Greenberg/Popp
> =A0 =A0/Kochhar/Whalen
> =A0 =A0/Kochhar/Mavris
> =A0 =A0/Kochhar/Baer
> =A0 =A0/Kochhar/Higgins
> =A0 =A0 =A0/Kochhar/Higgins/Gietz
>
> If I have Greenberg in EXCLUDED_NODES then output should be
>
> Path
> ---------------------------------------------------------------
> =A0/Kochhar
> =A0 =A0/Kochhar/Whalen
> =A0 =A0/Kochhar/Mavris
> =A0 =A0/Kochhar/Baer
> =A0 =A0/Kochhar/Higgins
> =A0 =A0 =A0/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 =3D 'Kochhar'
4 CONNECT BY PRIOR employee_id =3D 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 =3D 'Kochhar'
6 CONNECT BY PRIOR employee_id =3D manager_id)
7 where instr("Path", 'Greenberg') =3D 0;
Path
----------------------------------------------------------------------------=
----
/Kochhar
/Kochhar/Whalen
/Kochhar/Mavris
/Kochhar/Baer
/Kochhar/Higgins
/Kochhar/Higgins/Gietz
6 rows selected.
SQL>
David Fitzjarrell
|
