| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: I don't understand the results of my hierarchical query
kim schrieb:
> I have a table with nodes and an association table which stores the
> relationships between the nodes. I wanted to try using the hierarchical
> query on my tables, but I didn't get the results I expected. Here are
> my two tables:
>
> SQL> select id from nodes;
>
> ID
> -------
> 1
> 2
> 3
> 4
> 5
> 6
> 7
> 8
>
> 8 rows selected.
>
> SQL> select parent, child from graph;
>
> PARENT CHILD
> ------ -----
> 4 2
> 4 3
> 5 4
> 1 8
> 2 6
> 2 7
>
> 6 rows selected.
>
> Now here is my query:
> select RPAD(' ', 2*(LEVEL-1)) || ID ID, parent, LEVEL
> from nodes
> LEFT OUTER JOIN graph
> ON id = child
> CONNECT BY PRIOR ID = parent;
>
> ROWNUM ID PARENT LEVEL
> ------ ------- ------ ------
> 1 8 1 1
> 2 6 2 1
> 3 7 2 1
> 4 2 4 1
> 5 6 2 2
> 6 7 2 2
> 7 3 4 1
> 8 4 5 1
> 9 2 4 2
> 10 6 2 3
> 11 7 2 3
> 12 3 4 2
> 13 1 1
> 14 8 1 2
> 15 5 1
> 16 4 5 2
> 17 2 4 3
> 18 6 2 4
> 19 7 2 4
> 20 3 4 3
>
> 20 rows selected.
>
> I expected to see rows 13-20, but not all of the rest. Is this normal?
>
> Here are the results of just the join:
> select RPAD(' ', 2*(LEVEL-1)) || ID ID, parent
> from nodes
> LEFT OUTER JOIN graph
> ON id = child;
>
> ID PARENT
> ------- ------
> 2 4
> 3 4
> 4 5
> 8 1
> 6 2
> 7 2
> 1
> 5
>
> I read that Oracle 9i has problems with the JOIN and CONNECT BY, but I
> tried it with the old Oracle outer join and I get the same results. I'm
> using 9.2.0.5.0.
>
> thanks!
>
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10002.htm#i2066102
<quote>
START WITH Clause
Specify a condition that identifies the row(s) to be used as the root(s) of a hierarchical query. Oracle Database uses as root(s) all rows that satisfy this condition. If you omit this clause, then the database uses all rows in the table as root rows. The START WITH condition can contain a subquery, but it cannot contain a scalar subquery expression.
</quote>
You are probably looking for something like
scott_at_ORA102> r
1 select RPAD(' ', 2*(LEVEL-1)) || ID ID, parent, LEVEL 2 from nodes
3 LEFT OUTER JOIN graph 4 ON id = CHILD
ID PARENT LEVEL
---------- ---------- ----------
1 1
8 1 2
5 1
4 5 2
2 4 3
6 2 4
7 2 4
3 4 3
Best regards
Maxim Received on Mon Nov 20 2006 - 08:03:36 CST
 |
 |