Most useful thing is easy look at relater records. as Excell simple functionality for navigations, so I m lookin to solve problem for planning process in supply office departments with limits in materials, prices, items, money...Its DEMO for hierarchicaly views based on only ONE table, should be demonstrated on http://htmldb.oracle.com/pls/otn/f?p=28254:1
Tree hierarchical structures
Table plannavigacija have PK as ID column and NAME,CODE and IDID as FK with point to ID
code for select is
select * from plannavigacija where id in
with q as (
select level lvl,
id
from plannavigacija e
connect by prior id = idid
start with id = nvl(1,(SELECT COUNT(*) FROM PLANNAVIGACIJA))
)
select *
from (
-- employees above start with employee
select e.*
from plannavigacija e
connect by prior idid=id
start with id = nvl(1,(SELECT COUNT(*) FROM PLANNAVIGACIJA))
union
-- employees who are are peers of the start with employee
select e.*
from plannavigacija e
where level = (select lvl from q where id = nvl(1,(SELECT COUNT(*) FROM PLANNAVIGACIJA)))
connect by prior id = idid
start with id = nvl(1,(SELECT COUNT(*) FROM PLANNAVIGACIJA))
union
-- employees directly below start with employee
select e.*
from plannavigacija e
where idID = nvl(1,(SELECT COUNT(*) FROM PLANNAVIGACIJA))
) sub
connect by prior id = idid
start with id = nvl(1,(SELECT COUNT(*) FROM PLANNAVIGACIJA))
Where 1 is one variable :P1_X as start tree or root
