Database hierarchies
- From: "Aggarwal, Meenakshi" <Meenakshi.Aggarwal@xxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 12 Jan 2005 08:45:38 -0500
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
