pruning branches from a "connect by Prior" query

[mcoo...@csc.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:

S
--------------------
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

[Dmitry Loginov]

Just add " and type_code <> 'CD' " expression to the start with clause!

<mcoo...@csc.com> wrote in message
news:1139918622.6...@g44g2000cwa.googlegroups.com...

>I am having trouble pruning branches from a "connect by Prior" query

<skip>

> 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' ;
>

<skip>

Dmitry

[mcoo...@csc.com]

Ohmygod - how did I miss that !
I have been messing about with that query for hours.
Thanks
Martin