Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> Database hierarchies
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-lReceived on Wed Jan 12 2005 - 07:46:34 CST
![]() |
![]() |