connect by prior oracle

Question

I have a table that contains an hierarchic element

Table :

B is a foreign key coming from the same table

The query is :

SELECT level niveau, A
from table parent, table child
START WITH A IN
( 'P0','P1','C2')
CONNECT BY PRIOR  A= B

The result

Why the "2 C2" apears?

The result desired is in spite of it's not the correct, i:

`2 C2` appears because `C2` is a child of `P2`. – Vincent Malgrat Oct 05 '12 at 13:15 — Vincent Malgrat, Oct 05 '12 at 13:15

score 2 · Answer 1 · answered Oct 05 '12 at 13:23

Connect by condition is evaluated first, start with later. In your case C2 is both a child of P2 AND a root of the hierarchy. This is why it appears in your result twice.

From Oracle Documentaion

Oracle processes hierarchical queries as follows:

A join, if present, is evaluated first, whether the join is specified in the FROM clause or with WHERE clause predicates.

The CONNECT BY condition is evaluated.

Any remaining WHERE clause predicates are evaluated.

Oracle then uses the information from these evaluations to form the hierarchy using the following steps:

Oracle selects the root row(s) of the hierarchy--those rows that satisfy the START WITH condition.

Oracle selects the child rows of each root row. Each child row must satisfy the condition of the CONNECT BY condition with respect to one of the root rows.

Oracle selects successive generations of child rows. Oracle first selects the children of the rows returned in step 2, and then the children of those children, and so on. Oracle always selects children by evaluating the CONNECT BY condition with respect to a current parent row.

If the query contains a WHERE clause without a join, then Oracle eliminates all rows from the hierarchy that do not satisfy the condition of the WHERE clause. Oracle evaluates this condition for each row individually, rather than removing all the children of a row that does not satisfy the condition.

Oracle returns the rows in the order shown in Figure 9-1. In the diagram, children appear below their parents. For an explanation of hierarchical trees,

it is your data that is incorrect, not your query: C2, P2 pair should either go or be replaced with (C2,null) — ivanhoe1982, Oct 05 '12 at 14:27

score 0 · Answer 2 · answered Apr 29 '13 at 06:19

0

set one node to be the root node mean it must be start with one node then it will work for you I had the same problem I hope this help you

SELECT level niveau, A
from table parent, table child
START WITH A IN ='P1'
CONNECT BY PRIOR  A= B

answered Apr 29 '13 at 06:19

danarj

1,798
7
26
54

connect by prior oracle

2 Answers2