Demo the effect of WHERE on CONNECT BY : connect by « Analytical Functions « Oracle PL / SQL

1. Oracle PL / SQL
2. Analytical Functions
3. connect by

Demo the effect of WHERE on CONNECT BY

 
create table employee(
         emp_no                 integer         primary key
        ,lastname               varchar2(20)    not null
        ,mgr                    integer
);

insert into employee(emp_no,lastname,mgr)
              values(1,'Tomy',2);

insert into employee(emp_no,lastname,mgr)
              values(2,'Jacky',3);

insert into employee(emp_no,lastname,mgr)
              values(3,'Joey',4);

insert into employee(emp_no,lastname,mgr)
              values(4,'Janey',5);

select lpad(' ',2*level-1)||lastname
           AS name
    from employee
    where lastname !='Tomy'
    connect by mgr = prior emp_no
    start with emp_no = 2;

NAME                                                                            
--------------------------------------------------------------------------------
 Jacky    


drop table employee;


 

Related examples in the same category