Database hierarchies

  • From: "Aggarwal, Meenakshi" <Meenakshi.Aggarwal@xxxxxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Wed, 12 Jan 2005 08:45:38 -0500


I have parent-child-child-child-.... (multiple hierarchies). What is the
best approach to create tables in data warehouse, when user have no clarity
on levels of hierarchies and I need to consider future increase in levels of
hierarchies, so I need to plan a structure that can absorb future

I saw one example on Does anybody have better way...

select rpad('*',2*level,'*')||ename EmpName, dname,
  2             sys_connect_by_path( ename, '/' ) cbp
  3    from emp
  4   start with mgr is null
  5   connect by prior empno = mgr
  6   order SIBLINGS by ename
  7  /

EMPNAME                        DNAME          CBP
------------------------------ -------------- ------------------------------
**KING                         ACCOUNTING     /KING
****BLAKE                      SALES          /KING/BLAKE
******ALLEN                    SALES          /KING/BLAKE/ALLEN
******JAMES                    SALES          /KING/BLAKE/JAMES
******MARTIN                   SALES          /KING/BLAKE/MARTIN
******TURNER                   SALES          /KING/BLAKE/TURNER
******WARD                     SALES          /KING/BLAKE/WARD
****CLARK                      ACCOUNTING     /KING/CLARK
******MILLER                   ACCOUNTING     /KING/CLARK/MILLER
****JONES                      RESEARCH       /KING/JONES
******FORD                     RESEARCH       /KING/JONES/FORD
********SMITH                  RESEARCH       /KING/JONES/FORD/SMITH
******SCOTT                    RESEARCH       /KING/JONES/SCOTT
********ADAMS                  RESEARCH       /KING/JONES/SCOTT/ADAMS


Other related posts: