Re: about hierarchical query

"Erpa" <Erpa_at_email.it> wrote in message
news:cmt01j$2h8$1_at_newsread.albacom.net...
| hi all,
| how can I write an hierarchical query to view all level of my tree, but
| only the branches end with leaves that satisfy some conditions?
|
|
| for example, this is my tree in my hierarchical table:
|
| A1 A2
| / \ / \
| / \ / \
| B1 B2 B3 B4
| / \ / \ / \ / \
| C1 C2 C3 C4 C5 C6 C7 C8
|
| if only C1, C5 and C6 satisfy some conditions i wish to have this result
| from my query:
|
| A1 A2
| / /
| / /
| B1 B3
| / / \
| C1 C5 C6
|
| level node
| --------- ---------
| 1 A1
| 2 B1
| 3 C1
| 1 A2
| 2 B3
| 3 C5
| 3 C6
|
| thanks for your help
| Paolo

Paolo,

The WHERE clause works as advertised for Oracle hierarchical queries -- which means in this case it will return the qualifying nodes no matter where they occur in the hierarchy

What you really want, though, is to return multiple hierarchies based on qualifications of the leaf values -- so use a subquery in the START WITH clause and walk up the hierarchy, not down, i.e:

select level, e.*
from emp e
connect by prior mgr = empno
start with empno in (

      select empno
      from   emp
      where job = 'SALESMAN'
      )

Unfortunately, the rows are returned in the reverse order from what you want, and overlapping structures are not combined, and (apparently even in 10g) this hierarchical query cannot be directly nested in the from clause of a 2nd hierarchical query (which could be used on the rows of this query to return your desired results)

However, your sample shows that you are not dealing with a strict hierarchy, but with a type of network or matrix (B2 has two parents) which cannot be modeled in with a recursive relationship in a hierarchical table. Is that a typo?

++ mcs Received on Mon Nov 22 2004 - 06:31:02 CST