Utiliser des jointures de table avec une fonction récursive

Bonjour à tous,

Je suis en train d'utiliser la fonctionnalité d'oracle sur la récursivité.
Ca marche très bien lorsque j'utilise ceci :

Code:

---

1 2 3 4

select SYS_CONNECT_BY_PATH(ih.child_id, ' - '), level from INV_HIERARCHIES ih start with ih.parent_id = 5011528 connect by prior ih.child_id = ih.parent_id

---

En revanche lorsque je veux utiliser une jointure avec une autre table comment faire?
Ceci ne marche pas :

Code:

---

1 2 3 4 5 6

select SYS_CONNECT_BY_PATH(ih.child_id, ' - '), level, ii.NAME from INV_INVESTMENTS ii, INV_HIERARCHIES ih start with ih.parent_id = 5011528 connect by prior ih.child_id = ih.parent_id where ii.id = ih.child_id

---

Quelqu'un peut-il m'aider sur la bonne syntaxe?

Merci beaucoup.
Eric.

En positionnant bien le WHERE :

Code:

---

1 2 3 4 5 6

select SYS_CONNECT_BY_PATH(ih.child_id, ' - '), level, ii.NAME from INV_INVESTMENTS ii, INV_HIERARCHIES ih where ii.id = ih.child_id start with ih.parent_id = 5011528 connect by prior ih.child_id = ih.parent_id

---

Ou mieux :

Code:

---

1 2 3 4 5 6

select sys_connect_by_path(ih.child_id, ' - '), level, ii.name from inv_investments ii inner join inv_hierarchies ih on ih.child_id = ii.id START with ih.parent_id = 5011528 CONNECT by PRIOR ih.child_id = ih.parent_id

---

Notez bien aussi les remarques suivantes tirées de la documentation officielle :

Citation:

---

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.

---

Citation:

---

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.

---

Ca devrait eviter la jointure a faire avant la recherche recursive.

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

WITH INV_HIERARCHIES AS ( SELECT 1 AS id,null AS child_id FROM dual union ALL SELECT 2 , 1 FROM dual union ALL SELECT 3 , 1 FROM dual union ALL SELECT 4 , 2 FROM dual ), INV_INVESTMENTS AS ( SELECT 1 AS id,'Durand' AS name FROM dual union ALL SELECT 2 ,'Dufour' FROM dual union ALL SELECT 3 ,'Cheng' FROM dual union ALL SELECT 4 ,'Moise' FROM dual ) select hier,lv,name from ( SELECT SYS_CONNECT_BY_PATH(ih.child_id, ' - ') as hier, level as lv, ih.id FROM INV_HIERARCHIES ih start WITH ih.id = 1 connect BY prior ih.id = ih.child_id ) req JOIN INV_INVESTMENTS ii ON ii.id = req.id

---

Citation:

---

Envoyé par boutade80

Ca devrait eviter la jointure a faire avant la recherche recursive.
...

---

Oui mais, ça ne donne pas forcement le même résultat.