Discussion :

[Mafate]

Bonjour à tous,

Je travaille sous Oracle 10g et ne suis malheureusement pas spécialiste en PL/SQL même si j'ai déjà créé quelques fonctions.

Je vais essayer d'être clair pour vous expliquer mon besoin mais c'est un peu complexe, désolé d'avance... Voici le contexte :

Nous commercialisons des produits via des catalogues renouvelés chaque année. Certains produits présentés sur des catalogues passés peuvent être reconduits sur les catalogues suivants, ou pas.

Pour des questions de mise en page des catalogues, les produits d'une même gamme vendus sous différents coloris (ex. : des canapés) sont présentés au client avec une et une seule référence (appelée référence commerciale) à laquelle on associe des codes couleurs. Lorsque le client passe commande, il indique donc la référence commerciale et le code couleur. L'association "référence commerciale + code couleur" permet de retrouver quelle référence physique est concernée (une référence physique pour le canapé rouge, une autre pour le bleu).

Malheureusement, même si les références physiques qui se cachent derrière les références commerciales restent inchangées dans le temps, il n'en va pas de même pour les références commerciales : le canapé présent sur le catalogue de l'année dernière aura une référence commerciale différente de celle du même canapé reconduit sur le catalogue actuel (c'est balot, mais notre outil de production nous l'impose). Par contre, ces 2 références commerciales, qui concernent la même gamme de canapés, pointeront bien vers les mêmes références physiques en fonction des codes couleurs.

Pour compliquer l'affaire s'il en est besoin, un coloris présent sur un catalogue passé peut être abandonné sur le catalogue actuel si ses ventes ont été considérées comme insuffisantes. A contrario, de nouveaux coloris peuvent apparaitre pour cette gamme de canapés dans les catalogues à venir.

Voici à présent ce qu'on m'a demandé : nos chefs de produits voudraient pouvoir suivre la vente de la gamme de canapés (et de tous les autres produits évidemment !) via un code (que nous appellerons code modèle) qui regrouperait toutes les références commerciales successives (et donc toutes les références physiques qui leur sont rattachées) attribuées au canapé, et ce que les coloris soient toujours en vente ou qu'ils aient été abandonnés. Ce code modèle sera porté au final par les références physiques dans notre système d'information.

Je dois donc écrire un fonction PL/SQL qui prendra en paramètre une référence physique et me retournera son code modèle.

Pour faire simple, on dira que le code modèle retourné sera basé sur le code de la référence commerciale la plus récemment créée pour l'ensemble des références physiques de la gamme concernée. Ainsi, si la référence physique passée en paramètre de la fonction correspond à un colori abandonné, la référence commerciale retenue pour déterminer son code modèle ne sera pas la celle à laquelle elle était affectée mais la dernière créée, toutes références physiques de la gamme confondues.

J'ai donc besoin de connaître, en partant d'une référence physique donnée, la liste de toutes les références physiques de la gamme concernée pour au final obtenir la liste de toutes les références commerciales auxquelles elles ont été affectées. Lorsque j'aurai cette liste de références commerciales, je saurai déterminer celle que je dois conserver pour construire le code modèle attendu.

Voici le descriptif (simplifié) de la table ZREFAFF dont je dispose pour effectuer ce travail :

Code :

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

1
2
3
4
5
6
Name         Null     Type        
------------ -------- ------------
ZCODSUP_0    NOT NULL VARCHAR2(18)  --> code catalogue (code support)
ITMREF_0     NOT NULL VARCHAR2(60)  --> référence physique (item reference)
ZREFCOM_0    NOT NULL VARCHAR2(24)  --> référence commerciale
ZREFCOMDAT_0 NOT NULL DATE          --> date de création de la référence commerciale

Voici comment je peux déterminer manuellement, en partant d'une référence physique donnée, la liste exhaustive des références commerciales portant l'ensemble des références physiques de la gamme (chaque ordre reprend l'ordre complet précédent dans son IN) :


Première boucle :

Passe a1 : recherche des références commerciales contenant la référence physique passée en paramètre : 2 références commerciales retournées

Code :

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

SELECT DISTINCT zrefcom_0 FROM zrefaff WHERE itmref_0 IN('428317B');

Passe a2 : recherche des références physiques portées par les références commerciales retournées par la passe a1 : 4 références physiques retournées

Code :

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

SELECT DISTINCT itmref_0 FROM zrefaff WHERE zrefcom_0 IN (SELECT zrefcom_0 FROM zrefaff WHERE itmref_0 IN('428317B'));

Seconde boucle :

Passe b1 : recherche des références commerciales contenant les références physiques retournées par la passe a2 : 9 références commerciales retournées

Code :

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

SELECT DISTINCT zrefcom_0 FROM zrefaff WHERE itmref_0 IN(SELECT DISTINCT itmref_0 FROM zrefaff WHERE zrefcom_0 IN (SELECT zrefcom_0 FROM zrefaff WHERE itmref_0 IN('428317B')));

Passe b2 : recherche des références physiques portées par les références commerciales retournées par la passe b1 : 8 références physiques retournées

Code :

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

SELECT DISTINCT itmref_0 FROM zrefaff WHERE zrefcom_0 IN (SELECT DISTINCT zrefcom_0 FROM zrefaff WHERE itmref_0 IN(SELECT DISTINCT itmref_0 FROM zrefaff WHERE zrefcom_0 IN (SELECT zrefcom_0 FROM zrefaff WHERE itmref_0 IN('428317B'))));

Troisième boucle :

Passe c1 : recherche des références commerciales contenant les références physiques retournées par la passe b2 : 10 références commerciales retournées

Code :

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

SELECT DISTINCT zrefcom_0 FROM zrefaff WHERE itmref_0 IN(SELECT DISTINCT itmref_0 FROM zrefaff WHERE zrefcom_0 IN (SELECT DISTINCT zrefcom_0 FROM zrefaff WHERE itmref_0 IN(SELECT DISTINCT itmref_0 FROM zrefaff WHERE zrefcom_0 IN (SELECT zrefcom_0 FROM zrefaff WHERE itmref_0 IN('428317B')))));

Passe c2 : recherche des références physiques portées par les références commerciales retournées par la passe c1 : 8 références physiques retournées

Code :

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

SELECT DISTINCT itmref_0 FROM zrefaff WHERE zrefcom_0 IN (SELECT DISTINCT zrefcom_0 FROM zrefaff WHERE itmref_0 IN(SELECT DISTINCT itmref_0 FROM zrefaff WHERE zrefcom_0 IN (SELECT DISTINCT zrefcom_0 FROM zrefaff WHERE itmref_0 IN(SELECT DISTINCT itmref_0 FROM zrefaff WHERE zrefcom_0 IN (SELECT zrefcom_0 FROM zrefaff WHERE itmref_0 IN('428317B'))))));

A ce stade, je stoppe les recherches car la passe c2 me retourne un nombre de références physiques identique à celui de la passe b2 -> La liste des références commerciales que je retiendrai est donc celle retournée par la passe c1.

Il semblerait donc que j'aie besoin d'une fonction PL/SQL récursive acceptant en paramètre un VARCHAR2(60) lors de sa première exécution (-> la référence physique dont on recherche le code modèle). Lors de cette première exécution, elle effectuera le travail des passes a1 et a2 décrites ci-dessus puis s'appellera elle-même pour prendre en paramètre la liste des références physiques retournées par la passe a2. Le paramètre passé à la fonction à partir de la seconde passe sera donc une liste de VARCHAR2(60) et non plus un VARCHAR2(60) seul. Elle effectuera alors le travail des passes b1 et b2 et ainsi de suite jusqu'à ce quelle constate que les passes c2 et b2 retournent le même nombre d'enregistrements.

Dernière précision, je souhaiterais pouvoir utiliser cette fonction dans un ordre SQL du type : SELECT f_modele('MA REFERENCE PHYSIQUE') FROM dual;

Je ne sais malheureusement pas comment gérer le type de paramètre dont cette fonction a besoin pour fonctionner. Merci d'avance si êtes arrivés à me lire jusqu'au bout ! et encore plus si vous pouvez m'aider à résoudre ce problème.

N'hésitez pas à me poser des questions si je n'ai pas été clair.

Cordialement.

[orafrance]

pourquoi pas simplement une requête récursive avec CONNECT BY ?

[mnitu]

A partir de votre table essayez de fournir un jeu d’essaye avec le résultat attendu en précisant si nécessaire les règles à appliquer

[Mafate]

Bonsoir et merci de vous pencher sur mon problème.

@orafrance : Simplement parce que je ne connais ni la syntaxe des requêtes récursives, ni le fonctionnement de CONNECT BY (je devrais être formé d'ici quelques mois si tout va bien...), mais s'il y a moyen de s'en sortir sans PL/SQL, je suis preneur d'une solution ! Peut-être pouvez-vous vous appuyer sur le jeu d'essai ci-dessous pour que je comprenne bien de quoi il retourne ? Cela m'intéresse vivement !

@mnitu : Vous avez raison. Voici donc un jeu d'essai pour passer dans le concret. J'ai réduit la table ZREFAFF aux 2 seuls champs qui nous intéressent ici :

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
37
38
39
40
41
42
43
44
45
46
47
48
49
50
CREATE TABLE zrefaff (
  ITMREF_0     VARCHAR2(60) NOT NULL,
  ZREFCOM_0    VARCHAR2(24) NOT NULL
);
 
COMMIT;
 
INSERT INTO zrefaff VALUES('465117A','HL00958T');
INSERT INTO zrefaff VALUES('424275A','5046538A');
INSERT INTO zrefaff VALUES('465117A','5046538A');
INSERT INTO zrefaff VALUES('465119N','5046538A');
INSERT INTO zrefaff VALUES('424275A','GI16515P');
INSERT INTO zrefaff VALUES('465117A','GI16515P');
INSERT INTO zrefaff VALUES('465119N','GI16515P');
INSERT INTO zrefaff VALUES('49767005','GI16515P');
INSERT INTO zrefaff VALUES('49767006','GI16515P');
INSERT INTO zrefaff VALUES('49767007','GI16515P');
INSERT INTO zrefaff VALUES('424275A','EX03624D');
INSERT INTO zrefaff VALUES('428317B','EX03624D');
INSERT INTO zrefaff VALUES('465117A','EX03624D');
INSERT INTO zrefaff VALUES('465119N','EX03624D');
INSERT INTO zrefaff VALUES('424275A','HK03816J');
INSERT INTO zrefaff VALUES('465117A','HK03816J');
INSERT INTO zrefaff VALUES('465119N','HK03816J');
INSERT INTO zrefaff VALUES('49767006','HK03816J');
INSERT INTO zrefaff VALUES('49767007','HK03816J');
INSERT INTO zrefaff VALUES('424275A','JH05638H');
INSERT INTO zrefaff VALUES('465119N','JH05638H');
INSERT INTO zrefaff VALUES('49767005','JH05638H');
INSERT INTO zrefaff VALUES('49767006','JH05638H');
INSERT INTO zrefaff VALUES('49767007','JH05638H');
INSERT INTO zrefaff VALUES('49767008','JH05638H');
INSERT INTO zrefaff VALUES('49767005','HK20444N');
INSERT INTO zrefaff VALUES('424275A','6046538A');
INSERT INTO zrefaff VALUES('428317B','6046538A');
INSERT INTO zrefaff VALUES('465117A','6046538A');
INSERT INTO zrefaff VALUES('465119N','6046538A');
INSERT INTO zrefaff VALUES('424275A','HI00064N');
INSERT INTO zrefaff VALUES('465117A','HI00064N');
INSERT INTO zrefaff VALUES('465119N','HI00064N');
INSERT INTO zrefaff VALUES('49767006','HI00064N');
INSERT INTO zrefaff VALUES('49767007','HI00064N');
INSERT INTO zrefaff VALUES('424275A','HW04027J');
INSERT INTO zrefaff VALUES('465117A','HW04027J');
INSERT INTO zrefaff VALUES('465119N','HW04027J');
INSERT INTO zrefaff VALUES('49767006','HW04027J');
INSERT INTO zrefaff VALUES('49767007','HW04027J');
INSERT INTO zrefaff VALUES('12345678','87654321');
 
COMMIT;

Je souhaite obtenir la liste de toutes les références commerciales (ZREFCOM_0) auxquelles les références physiques (ITMREF_0) d'une gamme donnée ont été affectées, et cela en partant d'une des références physiques de cette gamme, en l'occurence la référence physique 428317B (cf. passe a1) de mon premier post.

Concrètement, je reprends les différentes passes indiquées dans mon premier message, qui vont par paires :

- Passe a1 : elle m'indique que la référence physique 428317B a été affectée à 2 références commerciales : EX03624D et 6046538A.
- Passe a2 : les 2 références commerciales retournées par la passe a1 ont eu 4 références physiques qui leur ont été affectées : 428317B (ma référence physique de départ), 465117A, 424275A et 465119N.

J'ai à présent la liste des 4 références physiques qui ont été affectées aux mêmes références comemrciales que ma référence physique de départ. Je peux recommencer le cycle :

- Passe b1 : elle m'indique que les 4 références physiques retournées par la passe a2 ont été affectées à 9 références commerciales : HL00958T, 5046538A, GI16515P, EX03624D, HK03816J, JH05638H, 6046538A, HI00064N et HW04027J.
- Passe b2 : les 9 références commerciales retournées par la passe b1 ont eu 8 références physiques qui leur ont été affectées : 465117A, 49767005, 428317B (je retrouve encre ici ma référence physique de départ, évidemment...), 49767008, 49767007, 424275A, 465119N et 49767006.

Je recommence un cycle en partant de ces 8 références physiques :

- Passe c1 : elle m'indique que les 8 références physiques retournées par la passe b2 ont été affectées à 10 références commerciales : HL00958T, GI16515P, 5046538A, EX03624D, HK03816J, JH05638H, HK20444N, HW04027J, HI00064N et 6046538A.
- Passe c2 : les 10 références commerciales retournées par la passe c1 ont eu 8 références physiques qui leur ont été affectées : la liste retournée ici est la même que celle retrounée par la passe b2.

Le fait les passes b2 et c2 retournent un nombre de références physiques identique montre que je suis arrivé au bout des recherches : si je continue les itérations, je n'augmenterai plus le nombre de références physiques ou commerciales retournées. Mes listes sont donc exhaustives.

Dans mon exemple, je retiendrai donc la liste des références comemrciales retournées par la passe c1.

Une fois que j'aurai obtenu cette liste, je saurai sélectionner la référence commerciale qui me permettra de retourner mon code modèle.

Voilà, j'espère avoir été plus clair que dans mon premier post. N'hésitez pas à revenir vers moi si vous avez d'autres questions/besoins pour comprendre mon problème et encore merci pour votre aide.

Je vous répondrai demain jeudi car je suis en rade de PC chez moi.

Bonne soirée et encore merci.

[Mafate]

Bonjour,

Je suis toujours sur mon problème et j'ai effectué quelques recherches concernant les requêtes récursives et l'ordre CONNECT BY.

Si je ne me trompe pas, d'après ce que j'ai pu lire sur le net, les requêtes récursives sont apparues sous Oracle avec la 11g release 2 et je travaille sous 10g. Je suis donc apparemment coincé de ce côté-ci...

Quant à l'ordre CONNECT BY, si je comprends bien les exemples que je trouve en googlant, il semble nécessiter une dépendance hiérarchique entre les 2champs qu'il utilise. Concrètement, dans l'exemple classique "champ parent / champ enfant", l'enfant doit aussi être un parent à un instant donné, et le parent un enfant (excepté évidemment pour les parents au plus haut de la hiérarchie). Dans mon cas, cela reviendrait à dire qu'une référence physique se trouverait être aussi une référence commerciale, et inversement, or ce n'est jamais le cas : les références physiques sont affectées aux références commerciales mais la hiérarchie s'arrête là.

Ceci dit, je débute dans le domaine donc je n'ai peut-être pas tout saisi. Merci de ne pas me taper sur la tête dans ce cas-là !

Merci encore pour votre aide et bonne journée.

[Mafate]

Re-bonjour,

Voilà, j'ai essayé de schématiser ce que je cherche à faire, toujours à partir du même jeu de données.



Comme on peut le voir, on part de la référence physique 428317B entourrée de rouge. On constate via la passe a1 qu'elle a été affectée aux 2 références commerciales elles aussi entourrées de rouge.

On repart ensuite de ces 2 références commerciales, à présent entourrées de bleu foncé, pour voir les références physiques qui leur sont affectées : c'est ce que nous donne la passe a2. On constate que 4 références physiques leur sont affectées (entourrées en bleu foncé dans la colonne de gauche).

On continue ce jeu de "ping-pong" tant que le nombre de références physiques retourné de cycle en cycle n'est pas stabilisé. Cette stabilisation se produit ici lors de la passe c2 qui retourne la même liste de références physiques que la passe b2. Les listes retournées n'évolueront donc plus et je peux conserver la liste de références commerciales désormais exhaustive retournée par la passe c1.

Je piocherai ensuite dans cette liste, en fonction de règles de gestion que l'on m'a fournies, la référence commerciale à conserver pour fabriquer mon code modèle et l'affecter à ma référence physique de départ entourrée en rouge. Et cette référence commerciale retenue ne sera pas forcément parmi l'une des 2 entourrées de rouge auxquelles la référence physique de départ est affectée. Par exemple, elle pourra être la HK20444N retournée par la passe c1. Par contre, cette référence commerciale sera la même qui servira à construire le code modèle de toutes les références physiques de la gamme (liste de gauche dans mon schéma). Ces références physiques auront donc au final toutes le même code modèle.

Voilà, j'espère que c'est plus clair pour vous car expliquer cela avec des mots n'est pas évident...

Bonne journée et encore merci pour votre aide.