Requête hiérarchique : incompréhension

[donny]

Bonjour,

J'ai une table CLIENT avec ses données

ID IDCODE IDMGR MCODE ZONE
1 B 1 B 8
2 B 3 B 8
3 B 3 B 8
4 B 3 B 8
5 B 4 B 8

et ce que je souhaiterais
c'est quand je passe un client à la requête(pour une zone) je ramène tous ses manager(mgr)
je remonte la hiérarchie.

ex : si je prend le client 5 B alors je dois ramener son mgr c'est à dire 4 B.
Comme 4 B a pour mgr 3 B , je dois alors aussi ramener 3 B
et comme 3 B a pour manager 3 B je m'arrête la,
car quand le client = le manager alors c'est qu'on est au sommet de la chaine.

j'ai testé cette requête :

Code :

1
2
3
SELECT * FROM CLIENT WHERE zone = 8
start WITH ( ID= 5 AND IDCODE= B AND zone = 8)
connect BY nocycle IDMGR = prior ID AND MCODE = prior IDCODE

le souci est qu'elle ne me ramène
que
5 B 4 B
et s'arrête , elle ne remonte pas la hiérarchie jusqu'au sommet.
au lieu de

5 B 4 B
4 B 3 B
3 B 3 B

Pourriez vous m’éclairer ?
j'ai sans douter oublié un truc dans la requête mais je ne vois pas

Merci

[Waldar]

Il faut déjà intervertir le PRIOR :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
WITH Client AS
(
SELECT 1 AS ID, 'B' AS IDCODE, 1 AS IDMGR, 'B' AS MCODE, 8 AS zone FROM dual union ALL
SELECT 2      , 'B'          , 3         , 'B'         , 8         FROM dual union ALL
SELECT 3      , 'B'          , 3         , 'B'         , 8         FROM dual union ALL
SELECT 4      , 'B'          , 3         , 'B'         , 8         FROM dual union ALL
SELECT 5      , 'B'          , 4         , 'B'         , 8         FROM dual
)
    SELECT ID, IDCODE, IDMGR, MCODE, ZONE, prior ID, prior IDMGR
      FROM CLIENT
start WITH ID = 5
       AND IDCODE = 'B'
       AND zone = 8
connect BY nocycle
           prior IDMGR = ID
       AND prior MCODE = IDCODE;
 
        ID I      IDMGR M       ZONE    PRIORID PRIORIDMGR
---------- - ---------- - ---------- ---------- ----------
         5 B          4 B          8                      
         4 B          3 B          8          5          4

Maintenant il manque la ligne 3.
Je pense qu'elle est filtrée à cause du NOCYCLE, comme elle boucle sur elle-même, le moteur la supprime du résultat.
Une petite astuce afin d'éviter la boucle est de gérer ce cas avec un nullif :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
    SELECT ID, IDCODE, IDMGR, MCODE, ZONE, prior ID, prior IDMGR
      FROM CLIENT
start WITH ID = 5
       AND IDCODE = 'B'
       AND zone = 8
connect BY prior nullif(IDMGR, ID) = ID
       AND prior IDCODE = MCODE;
 
        ID I      IDMGR M       ZONE    PRIORID PRIORIDMGR
---------- - ---------- - ---------- ---------- ----------
         5 B          4 B          8                      
         4 B          3 B          8          5          4
         3 B          3 B          8          4          3

À approfondir néanmoins.

[AbouZaid]

Bonjour,

Tu veux absolument une seule requête ou bien tu peux utiliser une boucle?

[donny]

merci waldar
je vais tester et donc cela proviendrait du nocycle

sinon oui
je peux utiliser une boucle
mais comme j'ai vu que les requete hierarchique existait je voulais d abord voir si c etait possible avec

[donny]

j'ai l'impression que le deuxieme cas ne marche pas
celui avec le nullif
quand je le lances j 'ai ceci

ORA-01436: CONNECT BY loop in user data

[donny]

je pense que c'est impossible de le faire sans passer par une boucle
ça a pas l'air vraiment au point le connect by

[Drizzt [Drone38]]

De mémoire, mais je ne suis pas un expert dans le domaine, une requete recursive ne pourrait-elle pas convenir ?

Tutoriel

[mnitu]

Si mais il faut au moins Oracle 11g

[donny]

je suis en 10g
c'est sans doute pour cela
par contre ce que je ne comprends pas
c'est quand j'utilise ma requête initiale
et que j'inverse le prior
comme ci-dessous :

Code :

1
2
3
SELECT * FROM CLIENT WHERE zone = 8
start WITH ( ID= 5 AND IDCODE= B AND zone = 8)
connect BY nocycle prior  IDMGR = ID AND prior  MCODE = IDCODE

elle me ramène énormément de lignes et des lignes qui n'ont rien avoir
???

[Waldar]

Envoyé par mnitu

Si mais il faut au moins Oracle 11g

Un petit complément : 11gR2 !

[donny]

pour que j essai de trouver une autre solution pourle faire,

je suis obliger de faire une fonction recursive en pl/sql ?

[Waldar]

Non pas nécessairement.
Sur le jeu de données fourni, une solution a été trouvée.

Vous dites que ça ne marche pas, mais sans nouveau jeu de données point de salut, je ne peux pas deviner ce qui ne fonctionne pas.

PS : connect by est une fonctionnalité qui existe depuis 1978, certes elle n'est pas parfaite (elle ne couvre pas tous les arbres récursifs contrairement aux CTE), mais on ne peut pas dire que ce n'est pas au point.

[laurentschneider]

Envoyé par Waldar

PS : connect by est une fonctionnalité qui existe depuis 1978, certes elle n'est pas parfaite (elle ne couvre pas tous les arbres récursifs contrairement aux CTE), mais on ne peut pas dire que ce n'est pas au point.

Bon, NOCYCLE a été introduit en version 10 et est totalement décevant à mon gout... NOCYCLE n'est utile à mon sense que pour identifier les lignes qui cyclent mais c'est trop mauvais pour obtenir des résultats consistents

Voir http://laurentschneider.com/wordpres...rsive-cte.html inspiré de http://www.developpez.net/forums/d74...-hierarchique/