requete complexe help

Salut,
Je suis sous oracle.
J'ai une table employer avec :
emp mgr
1 1
2 1
3 1
4 3
5 4
6 4
12 12
15 12
14 12
16 15
en faite
1 1 signifie le plus haut niveau, l employé est son propre manager:
les employé 2,3 on 1 comme manager
l employé 4 a 3 comme manager (sachant que 3 a un comme manager)
l employé 5 et 6 on 4 comme manager ( sachant que 4 a 3 comme manager)
puis 12 a 12 comme manager donc il est haut plus de la chaine et ainsi de suite ...
ce qui se passe c'est que je dois livrer que les manager et toute leur hierarchie qui se trouve dans une certaine table
j'ai la table livraison qui contient par exemple
id mgr
1 1
donc je dois livrer le manager 1 et tous ceux qui sont sous lui c'est a dire
tout cela uniquement
1 1
2 1
3 1
4 3
5 4
6 4
en gros le pere avec ses enfant et les enfant de ses enfants ...........
j'ai fait cette requete

Code:

---

1 2 3 4 5 6 7 8

select * from ( select emp,mgr from employer start with mgr = emp connect by nocycle mgr = prior emp ) E, livraison L where E.mgr = L.mgr

---

Le soucis est que cette requete s'arrete au premier niveau
elle me ramene ceci
1 1
2 1
3 1
Elle ne va pas chercher les enfant du 3 et du 4
4 3
5 4
6 4

JE ne comprends vraiment pas, parceque quand je lance unique la requete hierarchique
sans faire la jointure avec la table livraison
j obtiens toute la hierarchie.
Merci de votre aide

Citation:

---

Envoyé par donny

JE ne comprends vraiment pas, parceque quand je lance unique la requete hierarchique
sans faire la jointure avec la table livraison
j obtiens toute la hierarchie.

---

C'est normal après la jointure tu n'as plus les lignes où mgr est différent de 1.
Quelque chose comme ça :

Code:

---

1 2 3 4

SELECT emp,mgr FROM employer start WITH emp in (select distinct mgr from livraison) connect BY nocycle mgr = prior emp

---

test :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

SQL> with employer as ( 2 select 1 as emp, 1 as mgr from dual union all 3 select 2 as emp, 1 as mgr from dual union all 4 select 3 as emp, 1 as mgr from dual union all 5 select 4 as emp, 3 as mgr from dual union all 6 select 5 as emp, 4 as mgr from dual union all 7 select 6 as emp, 4 as mgr from dual union all 8 select 12 as emp, 12 as mgr from dual union all 9 select 15 as emp, 12 as mgr from dual union all 10 select 14 as emp, 12 as mgr from dual union all 11 select 16 as emp, 15 as mgr from dual 12 ), 13 livraison as (select 1 as id, 1 as mgr from dual) 14 SELECT emp,mgr 15 FROM employer 16 start WITH emp in (select distinct mgr from livraison) 17 connect BY nocycle mgr = prior emp;   EMP MGR ---------- ---------- 1 1 2 1 3 1 4 3 5 4 6 4   6 ligne(s) sÚlectionnÚe(s).

---

j ai pas tres bien compris la synthaxe avec le with
la synthaxe avec le in , je l'ai fait mais si dans ma table j ai beaucoup de ligne, question performance, c'est pas trop sa?
avec un exists c'est possible ?j'ai essayer mais ça ne marche pas
merci

Le faire en pl/sql c est moins couteux que le in
faire un curseur qui prend la table livraison et lancer la requete pour chaque emp = mgr et comme ç ane plus faire de IN ()

Je ne suis pas tres fort en requete hierarchique, mais pour le exists tu peux peut etre tenter comme ca :

Code:

---

1 2 3 4 5 6 7

with emp_liv as ( select e.mgr from employer e where exists (select 1 from livraison l where l.mgr=e.mgr) ) SELECT emp,mgr FROM employer start WITH emp IN (SELECT mgr FROM emp_liv) connect BY nocycle mgr = prior emp

---

Sinon pour le IN j'imagine que tu filtres quand meme la table livraison ?

Le WITH c'était juste pour générer un exemple avec vos tables.

Je pense que le PL/SQL sera nettement plus coûteux que le SQL récursif.

Vous pouvez aussi utiliser connect_by_root mgr pour garder le point de départ de votre récursivité.

skuatamad , ton exemple revient a faire ça

Code:

---

1 2 3 4

SELECT emp,mgr FROM employer start WITH emp IN (SELECT DISTINCT mgr FROM livraison) connect BY nocycle mgr = prior emp

---

je vais me retrouver avec le IN et comme j'ai beaucoup de donnée dans ma table sa va mettre une plombe;

Waldar, sans passé par le pl/sql je vois pas comment je pourrai faire, pour eviter le IN

Qu'entendez-vous par beaucoup de données ?

par beaucoup j entend 500 000 lignes

Citation:

---

Envoyé par donny

skuatamad , ton exemple revient a faire ça

Code:

---

1 2 3 4

SELECT emp,mgr FROM employer start WITH emp IN (SELECT DISTINCT mgr FROM livraison) connect BY nocycle mgr = prior emp

---

je vais me retrouver avec le IN et comme j'ai beaucoup de donnée dans ma table sa va mettre une plombe;

Waldar, sans passé par le pl/sql je vois pas comment je pourrai faire, pour eviter le IN

---

Pas tout a fait, dans mon 2eme exemple le with fait partie de la requete et si tu as bcp de lignes dans livraisons et pas des masses dans employer, ca peu etre avantageux de generer une table emp_liv.
Enfin c'est des suppositions, as tu teste la 2eme requete ?

PS:desole pour les accent, clavier qwerty

j ai tester la requete avec

Citation:

---

WITH emp_liv AS

---

en faite elle bloque au premier noeud

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36

SQL> WITH employer AS ( SELECT 1 AS emp, 1 AS mgr FROM dual union ALL SELECT 2 AS emp, 1 AS mgr FROM dual union ALL SELECT 3 AS emp, 1 AS mgr FROM dual union ALL SELECT 4 AS emp, 3 AS mgr FROM dual union ALL SELECT 5 AS emp, 4 AS mgr FROM dual union ALL SELECT 6 AS emp, 4 AS mgr FROM dual union ALL SELECT 9 AS emp, 9 AS mgr FROM dual union ALL SELECT 10 AS emp, 9 AS mgr FROM dual union ALL SELECT 12 AS emp, 12 AS mgr FROM dual union ALL SELECT 15 AS emp, 12 AS mgr FROM dual union ALL SELECT 14 AS emp, 12 AS mgr FROM dual union ALL SELECT 16 AS emp, 15 AS mgr FROM dual ), livraison AS (SELECT 1 AS id, 1 AS mgr FROM dual union all SELECT 2 AS id, 12 AS mgr FROM dual), emp_liv as (select e.mgr from employer e where exists (select 1 from livraison l where l.mgr=e.mgr)) SELECT emp,mgr FROM employer start WITH emp IN (SELECT mgr FROM emp_liv) connect BY nocycle mgr = prior emp;   EMP MGR ---------- ---------- 1 1 2 1 3 1 4 3 5 4 6 4 12 12 15 12 16 15 14 12   10 rows selected.

---

Qu'entends tu par elle bloque au 1er noeud?
niveau perf c'est trop lent? ou il manque des resultats ?

en faite, elle bloque au premier
c'est a dire que pour le pere elle ramen le fils et si le fils a un fils,elle ne le ramene pas;

dans employer j 'ai plus de ligne que dans livraison

je pensai a un truc
le faire avec un with mais en faisant dans le with la jointure entre employer et livraison et en faisant un select sur le resultat du with ,avec le connect by prior

j'ai essayer avec un with et sa fonctionne toujours pas,
ça me s arrete au premier niveau
elle me ramene les enfant de 1 mais pas les enfant des enfant de 1
je ne comprends pas,
est ce que ma requete est mal construite ?
est ce qu'il y a une subtilité que j 'ai oubmié, une synthaxe particuliere?
merci de vos aides

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12

WITH test AS ( select E.emp,E.mgr from employer E, livraison L E.mgr = L.mgr ) select emp,mgr from test start with mgr = emp connect by nocycle mgr = prior emp

---