Re: Hierarchical Subquery must omit nodes

On Apr 17, 10:09 am, "Chris L." <diver..._at_uol.com.ar> wrote:
> On Apr 16, 6:43 pm, Urs Metzger <u..._at_ursmetzger.de> wrote:
>
>
>
>
>
> > fitzjarr..._at_cox.net schrieb:
>
> > > On Apr 16, 2:21 pm, "Shakespeare" <what..._at_xs4all.nl> wrote:
> > >> "fitzjarr..._at_cox.net" <orat..._at_msn.com> schreef in berichtnews:57056886-02e4-4188-9b29-b7ddf517bbb7_at_a70g2000hsh.googlegroups.com...
> > >> On Apr 16, 12:16 pm, "Chris L." <diver..._at_uol.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
>
> 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"
> >    2     FROM (select *
> >    3             from employees
> >    4            where not exists (select *
> >    5                                      from excluded_nodes where last_name in (employee_id, manager_id) ))
> >    6     START WITH last_name = 'Kochhar'
> >    7     CONNECT BY PRIOR employee_id = manager_id;
>
> Appreciate it!
> Chris- Hide quoted text -
>
> - Show quoted text -

Your modification won't work; the 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 *
  2 from employees
  3 where not exists (select *

  4                    from excluded_nodes where last_name in
(employee_id, manager_id) );
                  from excluded_nodes where last_name in (employee_id,
manager_id) )
                                            *

The query supplied works as expected; if you're having trouble 'translating' it to your specific case then post your query and someone here can help you with the rewrite.

David Fitzjarrell Received on Thu Apr 17 2008 - 10:45:32 CDT