Discussion :

[donny]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[skuatamad]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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).

[donny]

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

[donny]

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 ()

[skuatamad]

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 ?

[Waldar]

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é.

[donny]

skuatamad , ton exemple revient a faire ça

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[Waldar]

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

[donny]

par beaucoup j entend 500 000 lignes

[skuatamad]

skuatamad , ton exemple revient a faire ça

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[donny]

j ai tester la requete avec

WITH emp_liv AS

en faite elle bloque au premier noeud

[skuatamad]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 ?

[donny]

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

[donny]

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

[donny]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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