On Apr 16, 2:21=A0pm, "Shakespeare" <what...@[EMAIL PROTECTED]
> wrote:
> "fitzjarr...@[EMAIL PROTECTED]
" <orat...@[EMAIL PROTECTED]
> schreef in
berichtnews:57056886-02=
e4-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/funct=
i...
>
> > 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"
> =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-----
> =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_name,
> '/') =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-----
> =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
'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
|
