On 10gR2 we want below hierarchical query to return only the branches which at least have one not NULL leaf ;
-- drop table corporate_slaves purge ;
create table corporate_slaves (
slave_id integer primary key,
supervisor_id references corporate_slaves,
name varchar(100), some_column number );
insert into corporate_slaves values (1, NULL, 'Big Boss ', NULL);
insert into corporate_slaves values (2, 1, 'VP Marketing', NULL);
insert into corporate_slaves values (9, 2, 'Susan ', NULL);
insert into corporate_slaves values (10, 2, 'Sam ', NULL);
insert into corporate_slaves values (3, 1, 'VP Sales', NULL);
insert into corporate_slaves values (4, 3, 'Joe ', NULL);
insert into corporate_slaves values (5, 4, 'Bill ', 5);
insert into corporate_slaves values (6, 1, 'VP Engineering', NULL);
insert into corporate_slaves values (7, 6, 'Jane ', NULL);
insert into corporate_slaves values (8, 6, 'Bob' , 3);
SELECT sys_connect_by_path(NAME, ' / ') path, some_column col, connect_by_isleaf isLeaf
FROM corporate_slaves
CONNECT BY PRIOR slave_id = supervisor_id
START WITH slave_id IN
(SELECT slave_id FROM corporate_slaves WHERE supervisor_id IS NULL) ;
For this example wanted output is like this one since Marketing has no NOT NULL some_column leaves where as Engineering and Sales has at least one;
PATH
--------------------------------------------------------------------------------
/ Big Boss
/ Big Boss / VP Sales
/ Big Boss / VP Sales / Joe
/ Big Boss / VP Sales / Joe / Bill
/ Big Boss / VP Engineering
/ Big Boss / VP Engineering / Jane
/ Big Boss / VP Engineering / Bob
Regards.