Database hierarchies

From: Aggarwal, Meenakshi <Meenakshi.Aggarwal_at_fishersci.com>
Date: Wed, 12 Jan 2005 08:45:38 -0500
Message-ID: <32729C17301C744582A747BD41569C12017CB2E5@PGHCR-EXMB-VS-1.na.fshrnet.com>

Hi,

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 hierarchies.

I saw one example on asktom.oracle.com. 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

Thanks

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jan 12 2005 - 07:46:34 CST