On Apr 17, 10:45=A0am, "fitzjarr...@[EMAIL PROTECTED]
" <orat...@[EMAIL PROTECTED]
> wrote:
> On Apr 17, 10:09=A0am, "Chris L." <diver...@[EMAIL PROTECTED]
> wrote:
>
>
>
>
>
> > 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:5705=
6886-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_NO=
DES
> > > >>> 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 =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
he=
lp
> > > >>> when the excluded node is somewhere deep in the hierarchy.
> > > >>> I've tried filtering the CONNECT BY but it cannot contain
subqueri=
es
> > > >>> (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"
> > > >> =A0 2 =A0 =A0 FROM employees
> > > >> =A0 3 =A0 =A0 START WITH last_name =3D 'Kochhar'
> > > >> =A0 4 =A0 =A0 CONNECT BY PRIOR employee_id =3D manager_id;
>
> > > >> Path
> > > >>
-------------------------------------------------------------------=
--------=AD=AD=AD-----
> > > >> =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
>
> > > >> 12 rows selected.
>
> > > >> -- 'Filtered' example
> > > >> SQL> select "Path"
> > > >> =A0 2 =A0from
> > > >> =A0 3 =A0(SELECT LPAD(' ',
2*level-1)||SYS_CONNECT_BY_PATH(last_nam=
e,
> > > >> '/') =A0"Path"
> > > >> =A0 4 =A0 =A0 FROM employees
> > > >> =A0 5 =A0 =A0 START WITH last_name =3D 'Kochhar'
> > > >> =A0 6 =A0 =A0 CONNECT BY PRIOR employee_id =3D manager_id)
> > > >> =A0 7 =A0where instr("Path", 'Greenberg') =3D 0;
>
> > > >> Path
> > > >>
-------------------------------------------------------------------=
--------=AD=AD=AD-----
> > > >> =A0/Kochhar
> > > >> =A0 =A0/Kochhar/Whalen
> > > >> =A0 =A0/Kochhar/Mavris
> > > >> =A0 =A0/Kochhar/Baer
> > > >> =A0 =A0/Kochhar/Higgins
> > > >> =A0 =A0 =A0/Kochhar/Higgins/Gietz
>
> > > >> 6 rows selected.
>
> > > >> SQL>
>
> > > >> David Fitzjarrell
>
> > > >> --------------------------------
> > > >> This will go wrong if you want to exclude 'Green' but include
'Gree=
nberg'
>
> > > >> Shakespeare- Hide quoted text -
>
> > > >> - Show quoted text -
>
> > > > A valid concern. =A0I 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"
> > > =A0 =A02 =A0 =A0 FROM (select *
> > > =A0 =A03 =A0 =A0 =A0 =A0 =A0 =A0 from employees
> > > =A0 =A04 =A0 =A0 =A0 =A0 =A0 =A0where last_name not in (select
last_na=
me
> > > =A0 =A05 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0
=
=A0 =A0 =A0 =A0from excluded_nodes))
> > > =A0 =A06 =A0 =A0 START WITH last_name =3D 'Kochhar'
> > > =A0 =A07 =A0 =A0 CONNECT BY PRIOR employee_id =3D manager_id;
>
> > > Path
> > >
----------------------------------------------------------------------=
-----=AD=AD-----
> > > =A0 /Kochhar
> > > =A0 =A0 /Kochhar/Whalen
> > > =A0 =A0 /Kochhar/Mavris
> > > =A0 =A0 /Kochhar/Baer
> > > =A0 =A0 /Kochhar/Higgins
> > > =A0 =A0 =A0 /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"
> > > =A0 =A02 =A0 =A0 FROM (select *
> > > =A0 =A03 =A0 =A0 =A0 =A0 =A0 =A0 from employees
> > > =A0 =A04 =A0 =A0 =A0 =A0 =A0 =A0where not exists (select *
> > > =A0 =A05 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0
=
=A0 =A0 =A0 =A0from excluded_nodes where last_name in (employee_id,
manager_=
id) ))
> > > =A0 =A06 =A0 =A0 START WITH last_name =3D 'Kochhar'
> > > =A0 =A07 =A0 =A0 CONNECT BY PRIOR employee_id =3D manager_id;
>
> > Appreciate it!
> > Chris- Hide quoted text -
>
> > - Show quoted text -
>
> Your modification won't work; =A0the 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 *
> =A0 2 =A0from employees
> =A0 3 =A0where not exists (select *
> =A0 4 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0from excluded_nodes where
las=
t_name in
> (employee_id, manager_id) );
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 from excluded_nodes where last_name
in=
(employee_id,
> manager_id) )
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0
=
=A0 =A0 =A0 =A0 *
> 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 =A0your query and
> someone here can help you with the rewrite.
>
> David Fitzjarrell- Hide quoted text -
>
> - Show quoted text -
Modifying your suggestion slightly produces the incorrect output:
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 =3D 'Kochhar'
7 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.
I would use the example as posted, and try to modify your original
query in a similar manner.
David Fitzjarrell
|
