pruning branches from a "connect by Prior" query

From: <mcoope29_at_csc.com>
Date: 14 Feb 2006 04:03:42 -0800
Message-ID: <1139918622.602534.237600@g44g2000cwa.googlegroups.com>

I am having trouble pruning branches from a "connect by Prior" query (oracle 8i)
My table looks like this
create table test_connect_by (

  parent     number,
  child      number,

Type_code varchar2(3)
);

insert into test_connect_by values ( 5, 2,'DP');
insert into test_connect_by values ( 5, 3,'DP');
insert into test_connect_by values (18,11,'XX');
insert into test_connect_by values (18, 7,'XX');
insert into test_connect_by values (17, 9,'KM');
insert into test_connect_by values (17, 8,'KM');
insert into test_connect_by values (26,13,'RA');
insert into test_connect_by values (26, 1,'RA');
insert into test_connect_by values (26,12,'RA');
insert into test_connect_by values (15,10,'CD');
insert into test_connect_by values (15, 5,'CD');
insert into test_connect_by values (38,15,'RC');
insert into test_connect_by values (38,17,'RK');
insert into test_connect_by values (38, 6,'RK');

My query looks like this:

select level || ' ' || lpad(' ',2*(level-1)) || to_char(Parent) || '
'|| to_char(child) || ' ' ||(type_code) s
  from test_connect_by
  start with parent =38
  connect by prior child = parent;

and my results look like this:

1 38 15 RC
2 15 10 CD
2 15 5 CD

3      5  2  DP
3      5  3  DP

1 38 6 RK
1 38 17 RK
2 17 9 KM
2 17 8 KM

9 rows selected.

so far so good, but I now want to exclude the branch where the Type_code ='RC'. Thus the query should exclude the first 5 rows above and return:

1 38 6 RK
1 38 17 RK
2 17 9 KM
2 17 8 KM

So I changed my query to :
select level || ' ' || lpad(' ',2*(level-1)) || to_char(Parent) || '
'|| to_char(child) || ' ' ||(type_code) s
  from test_connect_by
  start with parent =38
  connect by prior child = parent and type_code <> 'CD' ;

This gives the exact same result as before - 9 rows. The "and type_code <>" bit seems to be ignored if it refers to items at level 1.

It does work if the item is below level 1:

select level || ' ' || lpad(' ',2*(level-1)) || to_char(Parent) || '
'|| to_char(child) || ' ' ||(type_code) s
  from test_connect_by
  start with parent =38
  connect by prior child = parent and type_code <> 'CD' ;

returns:
S

1 38 15 RC
1 38 6 RK
1 38 17 RK
2 17 9 KM
2 17 8 KM

as I would expect.

Is there any way I can exclude branches at level 1 ?

The query is at the back end of a web page which currently allows a user to display the whole tree, starting at any parent. I want to add functionality to let the user specify a branch or branches to exclude. I thought it would be a minor change to the existing connect by clause, but if I can't make it work I may have to replace the whole query with my own SQL equivalent - not something I am looking forward to as I am a MS SQL bod, not Oracle...
Martin Received on Tue Feb 14 2006 - 06:03:42 CST