Discussion :

[billybob2]

Bonjour à tous,

Je voudrais mettre en place un système de codification de Produits Finis(PF) basé sur le principe suivant :

1) le PF est codifé comme suit : SERIE VAL_CAR1 VAL_CAR2 VAL_CAR3 ...VAL_CARN
2) Un PF appartient à une seule série
3) Cette série possède une liste de caractéristiques obligatoires ou optionnelles
4) Chaque caractéristique est définie par sa position dans la codification et comporte une liste de valeurs autorisées

Créer la structure n'est pas un problème.
Je veux créer une requête qui renvoie la liste exhaustive des PF d'une série afin de valider la création de nouveaux PF.

Un exemple valant toujours mieux qu'un long discours , voici les données d'entrée d'une codification:

Code :

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

1
2
3
4
5
6
7
8
SER_LIBELLE CAR_POS 	CAR_LIBELLE  	CAR_VALEUR	CAR_OBLIGATOIRE
S1		1   	MODELE   	1200   		1
S1		1   	MODELE   	1450   		1
S1		2   	COULEUR   	ROUGE		0
S1		2   	COULEUR   	VERT  		0
S1		3   	COND      	B100 		0
S1		4   	ROHS      	e2     		1     
S1		4   	ROHS      	e      		1

A partir de cette vue , je voudrais écrire la requête qui renvoie la liste exhaustive des Produits finis qui respectent cette codification :

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
S1 1200 e2
S1 1200 e
S1 1450 e2
S1 1450 e
S1 1200 ROUGE e2
S1 1200 ROUGE e
S1 1450 ROUGE e2
S1 1450 ROUGE e
S1 1200 VERT e2
S1 1200 VERT e
S1 1450 VERT e2
S1 1450 VERT e
S1 1200 B100 e2
S1 1200 B100 e
S1 1450 B100 e2
S1 1450 B100 e
S1 1200 ROUGE B100 e2
S1 1200 ROUGE B100 e
S1 1200 VERT B100 e2
S1 1200 VERT B100 e
S1 1450 ROUGE B100 e2
S1 1450 ROUGE B100 e
S1 1450 VERT B100 e2
S1 1450 VERT B100 e

Pour construire le PF j'ai utilisé la CTE récursive donnée par SQLPro Paragraphe IV-D.
j'ai essayé de l'adapter à ma problématique sans succés jusqu'ici car je bute sur la problématique de la génération des PF avec les caractéristiques optionnelles(Voir code SQL joint).

Si quelqu'un a une idée ?

Merci d'avance.

[aieeeuuuuu]

Bonjour,

Il me semble que votre deuxième CTE (dont le but est visiblement de filtrer pour n'avoir que les PF qui ont toutes les caractéristiques obligatoires) fonctionnera mal si la dernière caractéristique n'est pas obligatoire, je l'ai supprimée et remplacée par un NOT EXISTS :

est-ce que ceci vous donne ce que vous voulez

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
 
 
;WITH LST_COD(SER_LIBELLE,PF_LIBELLE,COD_POS)
AS
(
	SELECT 
			SER_LIBELLE,
			SER_LIBELLE + ' ' + CAST(CAR_VALEUR AS VARCHAR(MAX)),
			CAR_POSITION
	FROM		T_CODIFICATION_COD
	WHERE		SER_LIBELLE='S1' 
		AND	CAR_POSITION=1
 
	UNION ALL
 
	SELECT 
			SUI.SER_LIBELLE,
			PRE.PF_LIBELLE + ' ' + CAST(SUI.CAR_VALEUR AS VARCHAR(MAX)),
			SUI.CAR_POSITION
	FROM		LST_COD PRE
	INNER JOIN	T_CODIFICATION_COD SUI
		ON		SUI.CAR_POSITION > PRE.COD_POS 
		AND		NOT EXISTS( 
					SELECT		1
					FROM		T_CODIFICATION_COD tmp
					WHERE		tmp.cod_id = SUI.cod_id
						AND	tmp.CAR_POSITION > PRE.COD_POS
						AND	tmp.CAR_POSITION < SUI.CAR_POSITION
						AND	tmp.car_obligatoire = 1
				)
)
SELECT 
		COD.SER_LIBELLE,
		COD.PF_LIBELLE
FROM		LST_COD COD
WHERE		NOT EXISTS (
				SELECT	1
				FROM	T_CODIFICATION_COD tmp
				WHERE	tmp.CAR_POSITION > COD.COD_POS
				and		tmp.car_obligatoire = 1
			)
ORDER BY 
			COD.SER_LIBELLE,
			COD.PF_LIBELLE

[billybob2]

Bonjour et encore merci pour le coup de main.

Le résultat est correct pour l'exemple donné , par contre si je modifie la codification précédente , votre requête ne donne pas le résultat escompté.

Code :

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

1
2
3
4
5
 
UPDATE T_CODIFICATION_COD 
SET COD_OBLIGATOIRE=1
WHERE SER_LIBELLE='S1' AND CAR_LIBELLE='COULEUR'
GO

Si l'on exécute votre requête on obtient le résultat suivant qui n'est pas correct .

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
 
S1 1200 B100 e <- NOK
S1 1200 B100 e2 <- NOK
S1 1200 e <- NOK
S1 1200 e2 <- NOK
S1 1200 ROUGE B100 e
S1 1200 ROUGE B100 e2
S1 1200 ROUGE e
S1 1200 ROUGE e2
S1 1200 VERT B100 e
S1 1200 VERT B100 e2
S1 1200 VERT e
S1 1200 VERT e2
S1 1450 B100 e <-NOK
S1 1450 B100 e2 <-NOK
S1 1450 e <-NOK
S1 1450 e2 <-NOK
S1 1450 ROUGE B100 e
S1 1450 ROUGE B100 e2
S1 1450 ROUGE e
S1 1450 ROUGE e2
S1 1450 VERT B100 e
S1 1450 VERT B100 e2
S1 1450 VERT e
S1 1450 VERT e2

Je ne suis pas très à l'aise avec les CTE , si vous pouvez m'expliquer surtout la partie INNER JOIN SUI.CAR_POSITION > PRE.COD_POS AND NOT EXISTS ...

Merci encore pour votre aide

[aieeeuuuuu]

en effet, il y avait une erreur dans une jointure de la partie récursive, je faisais la jointure sur la colonne COD_ID, en pensant que cela identifiait la serie, mais ce n'est pas le cas. je fais donc la jointure sur SER_LIBELLE, a défaut de mieux.

Est-ce que cette requete vous donne ce que vous voulez ?

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
 
 
;WITH LST_COD(SER_LIBELLE,PF_LIBELLE,COD_POS)
AS
(
	SELECT 
			SER_LIBELLE,
			SER_LIBELLE + ' ' + CAST(CAR_VALEUR AS VARCHAR(MAX)),
			CAR_POSITION
	FROM		T_CODIFICATION_COD
	WHERE		SER_LIBELLE='S1' 
		AND	CAR_POSITION=1
 
	UNION ALL
 
	SELECT 
			SUI.SER_LIBELLE,
			PRE.PF_LIBELLE + ' ' + CAST(SUI.CAR_VALEUR AS VARCHAR(MAX)),
			SUI.CAR_POSITION
	FROM		LST_COD PRE
	INNER JOIN	T_CODIFICATION_COD SUI
		ON		SUI.CAR_POSITION > PRE.COD_POS 
		AND		NOT EXISTS( 
					SELECT		1
					FROM		T_CODIFICATION_COD tmp
					WHERE		tmp.SER_LIBELLE = SUI.SER_LIBELLE
						AND	tmp.CAR_POSITION > PRE.COD_POS
						AND	tmp.CAR_POSITION < SUI.CAR_POSITION
						AND	tmp.car_obligatoire = 1
				)
)
SELECT 
		COD.SER_LIBELLE,
		COD.PF_LIBELLE
FROM		LST_COD COD
WHERE		NOT EXISTS (
				SELECT	1
				FROM	T_CODIFICATION_COD tmp
				WHERE	tmp.CAR_POSITION > COD.COD_POS
				AND		tmp.car_obligatoire = 1
			)
ORDER BY 
			COD.SER_LIBELLE,
			COD.PF_LIBELLE

quant à cette partie là

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
INNER JOIN	T_CODIFICATION_COD SUI
		ON		SUI.CAR_POSITION > PRE.COD_POS 
		AND		NOT EXISTS( 
					SELECT		1
					FROM		T_CODIFICATION_COD tmp
					WHERE		tmp.SER_LIBELLE = SUI.SER_LIBELLE
						AND	tmp.CAR_POSITION > PRE.COD_POS
						AND	tmp.CAR_POSITION < SUI.CAR_POSITION
						AND	tmp.car_obligatoire = 1
				)

C'est dans la partie récursive de la requête, elle permet de trouver toutes les caractéristiques suivantes possibles ( SUI.CAR_POSITION > PRE.COD_POS ) sans qu'il n'y ait de caractéristiques obligatoire omise (rôle du not exists)

[billybob2]

Bonjour,

Dans la version finale il y aura un champs SER_ID(Integer) qui servira à faire le lien.

Il y a encore 2 soucis:
- lorsque la première caractéristique est optionnelle
- lorsque plusieurs séries sont rentrées dans la codification

Maintenant que j'ai compris le principe , j'ai corrigé et celà semble bien fonctionner .

Merci encore pour votre aide.

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
 
;WITH LST_COD(SER_LIBELLE,PF_LIBELLE,COD_POS)
AS
(
	SELECT 
			TOP 1 SER_LIBELLE,
			CAST(SER_LIBELLE AS VARCHAR(MAX)) ,
			CAST (0 AS TINYINT)
	FROM		T_CODIFICATION_COD
	WHERE		SER_LIBELLE='S1'
 
	UNION ALL
 
	SELECT 
			SUI.SER_LIBELLE,
			PRE.PF_LIBELLE + ' ' + CAST(SUI.CAR_VALEUR AS VARCHAR(MAX)),
			SUI.CAR_POSITION
	FROM		LST_COD PRE
	INNER JOIN	T_CODIFICATION_COD SUI
		ON		SUI.CAR_POSITION > PRE.COD_POS AND PRE.SER_LIBELLE=SUI.SER_LIBELLE
		AND		NOT EXISTS( 
					SELECT		1
					FROM		T_CODIFICATION_COD tmp
					WHERE		tmp.SER_LIBELLE = SUI.SER_LIBELLE
						AND	tmp.CAR_POSITION > PRE.COD_POS
						AND	tmp.CAR_POSITION < SUI.CAR_POSITION
						AND	tmp.car_obligatoire = 1
				)
)
SELECT 
		COD.SER_LIBELLE,
		COD.PF_LIBELLE
FROM		LST_COD COD
WHERE		NOT EXISTS (
				SELECT	1
				FROM	T_CODIFICATION_COD tmp
				WHERE	tmp.SER_LIBELLE=COD.SER_LIBELLE AND
						tmp.CAR_POSITION > COD.COD_POS
				AND		tmp.car_obligatoire = 1
			)
ORDER BY 
			COD.SER_LIBELLE,
			COD.PF_LIBELLE

[aieeeuuuuu]

Bonjour,

en effet, vos deux remarques sont justes. Par contre, sur la dernière requête que vous avez postée, il me semble que le cas de la première caractéristique optionnelle n'est pas correctement géré non plus : vous prenez toutes les caractéristiques, y compris celles ayant des caractéristiques obligatoires positionnées avant elles. Je dirai que la requête d'ancrage de votre récursive devrait sélectionner : "toutes les caractéristiques pour lesquelles il n'existe pas de caractéristique qui la précède ET qui soit obligatoire"
Il faut donc aussi supprimer le TOP 1, car la première caractéristique peut être soit celle qui a la position 1 (non obligatoire), soit celle qui a la position 2...

Ce qui doit donner (je remet juste l'ancrage) :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
	SELECT 
			SER_LIBELLE,
			CAST(SER_LIBELLE AS VARCHAR(MAX)) ,
			CAR_POSITION AS TINYINT
	FROM		T_CODIFICATION_COD A
	WHERE		SER_LIBELLE='S1'
 	AND 		NOT EXISTS(
				SELECT 1
				FROM T_CODIFICATION_COD tmp
				WHERE tmp.SER_LIBELLE = A.SER_LIBELLE
				AND tmp.CAR_POSITION < A.CAR_POSITION
				AND tmp.CAR_OBLIGATOIRE = 1
	)