René Nyffenegger's collection of things on the web | |
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
|
Flat Hierarchy with Oracle SQL | ||
create global temporary table stack(lvl number, val number) on commit delete rows; create table group_enumeration (empno number(4), mgr_no number(4)); begin for r in ( select level, empno, mgr from emp start with mgr is null connect by prior empno=mgr) loop delete from stack where lvl >= r.level; insert into group_enumeration select r.empno,val from stack; insert into stack values(r.level,r.empno); end loop; end; / select empno, mgr_no from group_enumeration where empno = 7369;
See also connect by.
|