Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

hierarchical sql-how to get only branches with at least one not null leaves

598210Nov 2 2007 — edited Nov 5 2010

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.

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 3 2010
Added on Nov 2 2007
9 comments
14,272 views