On Apr 16, 6:43 pm, Urs Metzger <u...@[EMAIL PROTECTED]
> wrote:
> fitzjarr...@[EMAIL PROTECTED]
schrieb:
>
> > On Apr 16, 2:21 pm, "Shakespeare" <what...@[EMAIL PROTECTED]
> wrote:
> >> "fitzjarr...@[EMAIL PROTECTED]
" <orat...@[EMAIL PROTECTED]
> schreef in
berichtnews:57056886=
-02e4-4188-9b29-b7ddf517bbb7@[EMAIL PROTECTED]
> >> On Apr 16, 12:16 pm, "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/server.920/a96540/fun=
cti...
> >>> SELECT LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(last_name, '/')
> >>> "Path"
> >>> FROM employees
> >>> START WITH last_name =3D 'Kochhar'
> >>> 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
> >>> 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 =3D 'Kochhar'
> >> 4 CONNECT BY PRIOR employee_id =3D manager_id;
>
> >> Path
> >>
-----------------------------------------------------------------------=
----=AD-----
> >> /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
> >>
-----------------------------------------------------------------------=
----=AD-----
> >> /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
'Greenber=
g'
>
> >> 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, '/')
"Pat=
h"
> 2 FROM (select *
> 3 from employees
> 4 where last_name not in (select last_name
> 5 from excluded_nodes))
> 6 START WITH last_name =3D 'Kochhar'
> 7 CONNECT BY PRIOR employee_id =3D 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, '/')
"Pat=
h"
> 2 FROM (select *
> 3 from employees
> 4 where not exists (select *
> 5 from excluded_nodes where
last_n=
ame in (employee_id, manager_id) ))
> 6 START WITH last_name =3D 'Kochhar'
> 7 CONNECT BY PRIOR employee_id =3D manager_id;
Appreciate it!
Chris
|
