Discussion :

[Catab]

Bonjour les internautes,

Pour commencer, mon environnement de production est une base de données DB2 mais l'environnement sur lequel je fais mes tests et qui concerne ce post est un serveur MySQL 8.0 hébergé en local sur ma machine.

Voici le contenu de ma base de données (version simplifiée de la DB de prod).

Création DB

Code :

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

1
2
3
4
5
6
 
DROP DATABASE IF EXISTS DBTEST;
 
CREATE DATABASE DBTEST CHARACTER SET 'utf8';
 
USE DBTEST;

Création tables

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
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
 
CREATE TABLE IF NOT EXISTS ART -- Articles
(
	ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
	REF VARCHAR(50) NOT NULL, -- Référence article
	DES VARCHAR(50), -- Description article
	TIERS VARCHAR(50), -- Fournisseur habituel
	PRIMARY KEY (ID)
) ENGINE = INNODB;
 
CREATE TABLE IF NOT EXISTS SART -- Sous-références articles
(
	ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
	REF VARCHAR(50) NOT NULL, -- Référence article
	SREF VARCHAR(50), -- Sous-référence article
	CR NUMERIC(20, 4), -- Coût de revient
	PRIMARY KEY (id)
) ENGINE = INNODB;
 
CREATE TABLE IF NOT EXISTS TFO -- Tarif achat
(
	ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
	REF VARCHAR(50) NOT NULL, -- Référence article
	SREF VARCHAR(50), -- Sous-référence article
	ETB VARCHAR(50), -- Etablissement
	TIERS VARCHAR(50), -- Fournisseur
	QTE INT, -- Quantité seuil d'application du tarif
	TADT DATE, -- Date d'application du tarif
	PA NUMERIC(20, 4), -- Prix d'achat net
	PRIMARY KEY (ID)
) ENGINE = INNODB;
 
CREATE TABLE IF NOT EXISTS RFO -- Condition fournisseur
(
	ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
	REF VARCHAR(50) NOT NULL, -- Référence article
	SREF VARCHAR(50), -- Sous-référence article
	ETB VARCHAR(50), -- Etablissement
	TIERS VARCHAR(50) NOT NULL, -- Fournisseur
	COECOD NUMERIC(20, 4), -- Pourcentage de coûts supplémentaires sur PA
	PRIMARY KEY (id)
) ENGINE = INNODB;
 
CREATE TABLE IF NOT EXISTS T019 -- Description sous-références articles
(
	ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
	SREF VARCHAR(50) NOT NULL, -- Sous-référence article
	DES VARCHAR(50), -- Description sous-référence article
	PRIMARY KEY (id)
) ENGINE = INNODB;
 
CREATE TABLE IF NOT EXISTS ETS -- Etablissements
(
	ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
	ETB VARCHAR(50) NOT NULL, -- Etablissement
	DES VARCHAR(50), -- Nom établissement
	PRIMARY KEY (ID)
) ENGINE = INNODB;
 
CREATE TABLE IF NOT EXISTS FOU -- Fournisseurs
(
	ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
	TIERS VARCHAR(50) NOT NULL, -- Fournisseur
	NOM VARCHAR(50), -- Nom fournisseur
	PRIMARY KEY (ID)
) ENGINE = INNODB;

Population tables

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
51
52
53
54
55
56
57
58
59
 
INSERT INTO ART
VALUES
(NULL, 'A', 'Article A', 'F1'),
(NULL, 'B', 'Article B', 'F2'),
(NULL, 'C', 'Article C', 'F3'),
(NULL, 'D', 'Article D', 'F4'),
(NULL, 'E', 'Article E', 'F1');
 
INSERT INTO SART
VALUES
(NULL, 'A', '', 1),
(NULL, 'B', 'SR1', 10),
(NULL, 'B', 'SR2', 11),
(NULL, 'B', 'SR3', 12),
(NULL, 'C', '', 20),
(NULL, 'D', '', 30);
(NULL, 'E', 'SR1', 1),
(NULL, 'E', 'SR2', 2),
(NULL, 'E', 'SR3', 3);
 
INSERT INTO RFO
VALUES
(NULL, 'E', '', '', 'F1', 1),
(NULL, 'E', 'SR2', '', 'F1', 2),
(NULL, 'E', 'SR2', 'E1', 'F1', 3),
(NULL, 'E', '', 'E3', 'F1', 4);
 
INSERT INTO TFO
VALUES
(NULL, 'E', '', '', 'F1', 0, '2019-01-01', 1),
(NULL, 'E', '', '', 'F1', 0, '2020-10-01', 1),
(NULL, 'E', 'SR1', '', 'F1', 0, '2020-10-01', 2),
(NULL, 'E', 'SR1', 'E1', 'F1', 0, '2020-10-01', 3),
(NULL, 'E', 'SR1', 'E1', 'F1', 10, '2020-10-01', 4),
(NULL, 'E', '', 'E2', 'F1', 0, '2020-10-01', 5),
(NULL, 'E', '', 'E2', 'F1', 20, '2020-10-01', 6),
(NULL, 'E', '', '', 'F1', 30, '2020-10-01', 7);
 
INSERT INTO T019
VALUES
(NULL, 'SR1', 'Sous-Ref 1'),
(NULL, 'SR2', 'Sous-Ref 2'),
(NULL, 'SR3', 'Sous-Ref 3');
 
INSERT INTO ETS
VALUES
(NULL, 'E1', 'LOG'), -- Plateforme Logistique
(NULL, 'E2', 'FLO'), -- Magasin
(NULL, 'E3', 'COG'), -- Magasin
(NULL, 'E4', 'DIN'); -- Magasin
 
INSERT INTO FOU
VALUES
(NULL, 'F1', 'Fournisseur 1'),
(NULL, 'F2', 'Fournisseur 2'),
(NULL, 'F3', 'Fournisseur 3'),
(NULL, 'F4', 'Fournisseur 4'),
(NULL, 'F5', 'LOG'); -- Plateforme Logistique en tant que fournisseur des autres établissements

Quelques explications s'imposent

Un article peut être géré en sous-référence ou non.
Par exemple, un pantalon peut être sous-référencé par taille.
Un article, qu'il soit géré en sous-référence ou non, a toujours une entrée dans la table SART. Car c'est dans cette table qu'est enregistré, entre autres, le CR de l'article (coût de revient calculé à des fins de valorisation de stock)
- S'il n'est pas géré en sous-référence, le champ SREF est une chaine de caractère vide (et pas NULL).
- S'il est géré en sous-référence, il y a une entrée pour chaque sous-référence représentée par le champ SREF et aucune entrée avec le champ SREF vide.

La table RFO contient des conditions d'achat fournisseur, ici COECOD, un coût en pourcentage à appliquer sur le tarif d'achat d'un article, qui dépendent de :
- L'article (ou la sous-référence de l'article)
- Notre établissement (magasin)
- Le fournisseur (un article peut avoir plusieurs fournisseurs)
IMPORTANT :
- Si le champ ETB est vide (et pas NULL encore une fois), alors cette entrée est valable pour tous les établissements.
- De même, si le champ SREF est vide, cette entrée est valable pour toutes les sous-références d'un article, s'il en a.
- Si ces deux champs sont vides, l'entrée est valable aussi bien pour tous les établissements que toutes les sous-références de l'article.
- Les champs REF et TIERS ne peuvent jamais être vides.

La table TFO contient les tarifs d'achat des article, simplifié ici par PA, le prix d'achat net, qui dépendent de :
- L'article (ou une sous-ref)
- Un établissement
- Un fournisseur
- Une quantité seuil d'achat à atteindre pour que ce tarif soit valable
Mêmes remarques que pour RFO :
- Si le champ ETB est vide, alors cette entrée est valable pour tous les établissements.
- De même, si le champ SREF est vide, cette entrée est valable pour toutes les sous-références d'un article, s'il en a.
- Si ces deux champs sont vides, l'entrée est valable aussi bien pour tous les établissements que toutes les sous-références de l'article.
- Le champ TIERS peut être vide puisqu'en théorie un tarif d'achat peut être valable pour tous les fournisseurs mais en pratique il ne l'est jamais.

Ma requête

A partir de là, j'aimerais obtenir :
- Le CR,
- Le COECOD,
- Le PA
pour chaque "cas possible" pour chaque sous-référence. Mais uniquement pour les derniers tarifs en date.
Afin, par après, de calculer la différence entre le CR et le PA majoré du COECOD : CR - (PA * (1 + COECOD))

J'ai volontairement créé l'article "E" avec des valeurs pour certaines sous-ref dans TFO mais uniquement une valeur par défaut dans RFO et vice versa. Et de-même pour les établissements. Il y a également un tarif antérieur expiré. Afin de couvrir un maximum de cas de figure.
Je vous épargne mes autres tests sur les articles A à D.

Dans cet exemple :
La sous référence SR1 dispose de son propre tarif d'achat.
Cette même sous-référence dispose d'un tarif spécial pour l'établissement 1 et dispose d'un tarif quantitatif sur cet établissement pour une quantité seuil de 10 unités.
L'établissement 2 dispose de son propre tarif d'achat pour toutes les sous-références de l'article E et il y a un tarif quantitatif à partir de 20 unités sur cet établissement.
Toutes les sous-référence de cet article ont un tarif quantitatif spécial à partir de 30 unités sur tous les établissements.
La SR2 a ses propres conditions fournisseurs, dont une spéciale valable pour l'établissement 1.
L'établissement 3 a ses propres conditions fournisseur pour toutes les sous-références de l'article E.

Puisque j'ai plus de mal de vous expliquer le résultat que je souhaite que vous le montrer, voici le résultat attendu, si mes calculs sont bons :

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
+-----+------+-----+-------+-----+------------+--------+--------+--------+----------+
| REF | SREF | ETB | TIERS | QTE | TADT       | CR     | COECOD | PA     | POSITION |
+-----+------+-----+-------+-----+------------+--------+--------+--------+----------+
| E   | SR1  |     | F1    |   0 | 2020-10-01 | 1.0000 | 1.0000 | 2.0000 |        1 |
| E   | SR1  | E1  | F1    |   0 | 2020-10-01 | 1.0000 | 1.0000 | 3.0000 |        1 |
| E   | SR1  | E1  | F1    |  10 | 2020-10-01 | 1.0000 | 1.0000 | 4.0000 |        1 |
| E   | SR1  | E2  | F1    |   0 | 2020-10-01 | 1.0000 | 1.0000 | 5.0000 |        1 |
| E   | SR1  | E2  | F1    |  20 | 2020-10-01 | 1.0000 | 1.0000 | 6.0000 |        1 |
| E   | SR1  |     | F1    |  30 | 2020-10-01 | 1.0000 | 1.0000 | 7.0000 |        1 |
| E   | SR1  | E3  | F1    |   0 | 2020-10-01 | 1.0000 | 4.0000 | 2.0000 |        1 |
| E   | SR1  | E3  | F1    |  30 | 2020-10-01 | 1.0000 | 4.0000 | 2.0000 |        1 |
| E   | SR2  |     | F1    |   0 | 2020-10-01 | 2.0000 | 2.0000 | 1.0000 |        1 |
| E   | SR2  |     | F1    |  30 | 2020-10-01 | 2.0000 | 2.0000 | 7.0000 |        1 |
| E   | SR2  | E2  | F1    |   0 | 2020-10-01 | 2.0000 | 2.0000 | 5.0000 |        1 |
| E   | SR2  | E2  | F1    |  20 | 2020-10-01 | 2.0000 | 2.0000 | 6.0000 |        1 |
| E   | SR2  | E3  | F1    |   0 | 2020-10-01 | 2.0000 | 4.0000 | 1.0000 |        1 |
| E   | SR2  | E3  | F1    |  30 | 2020-10-01 | 2.0000 | 4.0000 | 7.0000 |        1 |
| E   | SR2  | E1  | F1    |   0 | 2020-10-01 | 2.0000 | 3.0000 | 1.0000 |        1 |
| E   | SR2  | E1  | F1    |  30 | 2020-10-01 | 2.0000 | 3.0000 | 7.0000 |        1 |
| E   | SR3  |     | F1    |   0 | 2020-10-01 | 3.0000 | 1.0000 | 1.0000 |        1 |
| E   | SR3  |     | F1    |  30 | 2020-10-01 | 3.0000 | 1.0000 | 7.0000 |        1 |
| E   | SR3  | E2  | F1    |   0 | 2020-10-01 | 3.0000 | 1.0000 | 5.0000 |        1 |
| E   | SR3  | E2  | F1    |  20 | 2020-10-01 | 3.0000 | 1.0000 | 6.0000 |        1 |
| E   | SR3  | E3  | F1    |   0 | 2020-10-01 | 3.0000 | 4.0000 | 1.0000 |        1 |
| E   | SR3  | E3  | F1    |  30 | 2020-10-01 | 3.0000 | 4.0000 | 7.0000 |        1 |
+-----+------+-----+-------+-----+------------+--------+--------+--------+----------+

Mon SELECT

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
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
 
SELECT
T1.REF AS REF,
T1.SREF AS SREF,
T1.ETB AS ETB,
T1.TIERS AS TIERS,
T1.QTE AS QTE,
T1.TADT AS TADT,
COALESCE(RFO.COECOD, RFO2.COECOD, RFO3.COECOD) AS COECOD,
SART.CR AS COUTREVIENT,
T1.PA AS PA,
T1.POSITION AS POSITION
 
FROM
(
	SELECT
	ROW_NUMBER() OVER(PARTITION BY TFO.REF, TFO.SREF, TFO.ETB, TFO.TIERS, TFO.QTE ORDER BY TFO.TADT DESC) AS POSITION,
 	TFO.ID,
 	TFO.TIERS,
 	TFO.ETB,
 	TFO.REF,
 	TFO.SREF,
 	TFO.TADT,
 	TFO.QTE,
 	TFO.PA
 
	FROM
	TFO
) AS T1
 
LEFT JOIN
RFO
ON 
(
	T1.REF = RFO.REF
	AND T1.SREF = RFO.SREF
	AND T1.TIERS = RFO.TIERS
	AND T1.ETB = RFO.ETB
)
 
LEFT JOIN
ART
ON (T1.REF = ART.REF)
 
LEFT JOIN
SART
ON (T1.REF = SART.REF
	AND T1.SREF = SART.SREF)
 
LEFT JOIN
RFO AS RFO2
ON
(
	T1.REF = RFO2.REF
	AND T1.SREF = RFO2.SREF
	AND T1.TIERS = RFO2.TIERS
	AND RFO2.ETB = ''
)
 
LEFT JOIN
RFO AS RFO3
ON
(
	T1.REF = RFO3.REF
	AND T1.SREF = ''
	AND T1.TIERS = RFO3.TIERS
	AND RFO2.ETB = RFO3.ETB
)
 
LEFT JOIN
RFO AS RFO4
ON
(
	T1.REF = RFO4.REF
	AND T1.SREF = ''
	AND T1.TIERS = RFO4.TIERS
	AND RFO2.ETB = ''
)
 
WHERE
T1.POSITION = 1
AND T1.REF = 'E'
 
UNION
 
SELECT
RFO.REF AS REF,
RFO.SREF AS SREF,
RFO.ETB AS ETB,
RFO.TIERS AS TIERS,
COALESCE(T1.QTE, T2.QTE, T3.QTE, T4.QTE) AS QTE,
COALESCE(T1.TADT, T2.TADT, T3.TADT, T4.TADT) AS TADT,
SART.CR AS CR,
RFO.COECOD AS COECOD,
COALESCE(T1.PA, T2.PA, T3.PA, T4.PA) AS PA,
COALESCE(T1.POSITION, T2.POSITION, T3.POSITION, T4.POSITION) AS POSITION
 
FROM
RFO
 
LEFT JOIN
(
	SELECT
	ROW_NUMBER() OVER(PARTITION BY TFO.REF, TFO.SREF, TFO.ETB, TFO.TIERS, TFO.QTE ORDER BY TFO.TADT DESC) AS POSITION,
 	TFO.ID,
 	TFO.TIERS,
 	TFO.ETB,
 	TFO.REF,
 	TFO.SREF,
 	TFO.TADT,
 	TFO.QTE,
 	TFO.PA
 
	FROM
	TFO
) AS T1
ON (T1.REF = RFO.REF
	AND T1.SREF = RFO.SREF
	AND T1.TIERS = RFO.TIERS
	AND T1.ETB = RFO.ETB)
 
LEFT JOIN
(
	SELECT
	ROW_NUMBER() OVER(PARTITION BY TFO.REF, TFO.SREF, TFO.ETB, TFO.TIERS, TFO.QTE ORDER BY TFO.TADT DESC) AS POSITION,
 	TFO.ID,
 	TFO.TIERS,
 	TFO.ETB,
 	TFO.REF,
 	TFO.SREF,
 	TFO.TADT,
 	TFO.QTE,
 	TFO.PA
 
	FROM
	TFO
) AS T2
ON (T2.REF = RFO.REF
	AND T2.SREF = RFO.SREF
	AND T2.TIERS = RFO.TIERS
	AND T2.ETB = '')
 
LEFT JOIN
(
	SELECT
	ROW_NUMBER() OVER(PARTITION BY TFO.REF, TFO.SREF, TFO.ETB, TFO.TIERS, TFO.QTE ORDER BY TFO.TADT DESC) AS POSITION,
 	TFO.ID,
 	TFO.TIERS,
 	TFO.ETB,
 	TFO.REF,
 	TFO.SREF,
 	TFO.TADT,
 	TFO.QTE,
 	TFO.PA
 
	FROM
	TFO
) AS T3
ON (T3.REF = RFO.REF
	AND T3.SREF = ''
	AND T3.TIERS = RFO.TIERS
	AND T3.ETB = RFO.ETB)
 
LEFT JOIN
(
	SELECT
	ROW_NUMBER() OVER(PARTITION BY TFO.REF, TFO.SREF, TFO.ETB, TFO.TIERS, TFO.QTE ORDER BY TFO.TADT DESC) AS POSITION,
 	TFO.ID,
 	TFO.TIERS,
 	TFO.ETB,
 	TFO.REF,
 	TFO.SREF,
 	TFO.TADT,
 	TFO.QTE,
 	TFO.PA
 
	FROM
	TFO
) AS T4
ON (T4.REF = RFO.REF
	AND T4.SREF = ''
	AND T4.TIERS = RFO.TIERS
	AND T4.ETB = '')
 
LEFT JOIN
ART
ON (RFO.REF = ART.REF)
 
LEFT JOIN
SART
ON (RFO.REF = SART.REF
	AND RFO.SREF = SART.SREF)
 
WHERE
COALESCE(T1.POSITION, T2.POSITION, T3.POSITION, T4.POSITION) = 1
AND RFO.REF = 'E'

Voilà le bébé auquel j'ai donné naissance...
Tout d'abord, j'ai fait en sorte de pouvoir isoler le dernier tarif en date en partitionnant sur TADT ma table TFO par REF, SREF, ETB, TIERS et QTE et en filtrant sur la POSITION.
Ensuite, parce qu'il me fallait tous les cas de figure possibles aussi bien avec les SREF et ETB de TFO que de RFO, j'ai fait un UNION entre deux requêtes. (En gros j'ai émulé un FULL JOIN)
- La première utilisant TFO comme point de départ.
- l'autre RFO.
Enfin, parcequ'il me faut aussi les valeurs par défaut lorsqu'une jointure n'existe pas entre TFO.ETB et RFO.ETB ou TFO.SREF et RFO.SREF, j'ai créé respectivement 4 sous-requêtes RFO lorsque TFO est ma source, et 4 sous-requêtes TFO lorsque RFO est ma source, dans lesquelles mes requêtes vont puiser les infos.
- La première joint RFO et TFO lorsque ETB et SREF sont égaux. (cas de correspondance exacte)
- La seconde joint RFO et TFO lorsque les SREF sont égaux mais que ETB est vide (correspondance uniquement sur la SREF)
- La troisième joint RFO et TFO lorsque les ETB sont égaux mais que SREF est vide (correspondance uniquement sur le ETB)
- La quatrième joint RFO et TFO uniquement sur base de la REF article et TIERS. Les valeurs de ETB et SREF sont vides. (Jointure par défaut ultime).
Je teste succèssivement ces jointures grâce à l'expression COALESCE.

Cette requête aboutissait à un résultat assez concluant dans mes autres tests, mais ici, il me manque des résultats, notamment tous les résultats sur le SR3 qui ne sont présents ni dans RFO ni dans TFO alors que cette sous-référence a elle-aussi des "cas possibles".

Et... je sèche un peu. Etes-vous plus inspiré que moi ?

Merci d'avance pour votre aide.

Bien à vous,

EDIT : Correction dans le résultat attendu :
REF = A | SREF = SR1 |ETB = E3 | QTE = 0
-> TFO.PA = 2 et pas 1

REF = A | SREF = SR1 |ETB = E3 | QTE = 30
-> TFO.PA = 2 et pas 7
La sous-référence a priorité sur la quantité commandée.

[Catab]

J'ai trouvé un résultat bien meilleur.

Pour un problème de cette complexité, j'ai découpée ma requête en vues que j'appelle via une requête principale.

TFODESC : Vue sur TFO partitionné pour obtenir un une position par date de tarif

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
 
CREATE OR REPLACE VIEW TFODESC
AS
SELECT
ROW_NUMBER() OVER(PARTITION BY TFO.REF, TFO.SREF, TFO.ETB, TFO.TIERS, TFO.QTE ORDER BY TFO.TADT DESC) AS POSITION,
TFO.ID,
TFO.TIERS,
TFO.ETB,
TFO.REF,
TFO.SREF,
TFO.TADT,
TFO.QTE,
TFO.PA
 
FROM
TFO;

SARTRFO : Vue joignant SART et RFO :

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
 
CREATE OR REPLACE VIEW SARTRFO
AS 
SELECT DISTINCT
SART.REF,
SART.SREF,
SART.CR,
COALESCE(RFO.ETB, RFO2.ETB) AS ETB,
COALESCE(RFO.COECOD, RFO2.COECOD) AS COECOD
 
FROM
SART
 
LEFT JOIN
RFO
ON (SART.REF = RFO.REF
	AND SART.SREF = RFO.SREF)
 
LEFT JOIN
RFO AS RFO2
ON (SART.REF = RFO2.REF
	AND RFO2.SREF = '');

SARTTFO : Vue joignant SART et ma vue TFODESC

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
 
CREATE OR REPLACE VIEW SARTTFO
AS
SELECT DISTINCT
SART.REF AS REF,
SART.SREF AS SREF,
SART.CR AS CR,
COALESCE(TFO.ETB, TFO2.ETB) AS ETB,
COALESCE(TFO.QTE, TFO2.QTE) AS QTE,
COALESCE(TFO.TADT, TFO2.TADT) AS TADT,
COALESCE(TFO.PA, TFO2.PA) AS PA,
COALESCE(TFO.TIERS, TFO2.TIERS) AS TIERS,
COALESCE(TFO.POSITION, TFO2.POSITION) AS POSITION
 
FROM
SART
 
LEFT JOIN
TFODESC AS TFO -- Vue
ON (SART.REF = TFO.REF
	AND SART.SREF = TFO.SREF)
 
LEFT JOIN
TFODESC AS TFO2 -- Vue
ON (SART.REF = TFO2.REF
	AND TFO2.SREF = '');

J'ai ensuite émulé un FULL JOIN entre mes vues SARTRFO et SARTTFO grâce à UNION :
(j'utilise UNION et pas UNION ALL parce que UNION ALL me retourne des résultats en trop que je pourrai certainement éviter mais qui demande une réflexion supplémentaire dont je me passe volontié)
(idem pour SELECT DISTINCT, j'ai des résultats en trop lorsque je ne mentionne pas DISTINCT en appelant mes vues. Et je comprends bien d'où ceux-ci proviennent, mais comme pour UNION, je me passerai de chercher à résoudre ce casse-tête avec plaisir)

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
51
52
 
SELECT DISTINCT
SARTRFO.REF,
SARTRFO.SREF,
SARTRFO.ETB,
COALESCE(SARTTFO.QTE, SARTTFO2.QTE) AS QTE,
SARTRFO.CR,
SARTRFO.COECOD,
COALESCE(SARTTFO.PA, SARTTFO2.PA) AS PA,
COALESCE(SARTTFO.POSITION, SARTTFO2.POSITION) AS POSITION
 
FROM
SARTRFO -- Vue
 
LEFT JOIN
SARTTFO -- Vue
ON (SARTTFO.REF = SARTRFO.REF
	AND SARTTFO.SREF = SARTRFO.SREF
	AND SARTTFO.ETB = SARTRFO.ETB)
 
LEFT JOIN
SARTTFO AS SARTTFO2 -- Vue
ON (SARTTFO2.REF = SARTRFO.REF
	AND SARTTFO2.SREF = SARTRFO.SREF
	AND SARTTFO2.ETB = '')
 
UNION
 
SELECT DISTINCT
SARTTFO.REF,
SARTTFO.SREF,
SARTTFO.ETB,
SARTTFO.QTE,
SARTTFO.CR,
COALESCE(SARTRFO.COECOD, SARTRFO2.COECOD) AS COECOD,
SARTTFO.PA,
SARTTFO.POSITION
 
FROM
SARTTFO -- Vue
 
LEFT JOIN
SARTRFO -- Vue
ON (SARTTFO.REF = SARTRFO.REF
	AND SARTTFO.SREF = SARTRFO.SREF
	AND SARTTFO.ETB = SARTRFO.ETB)
 
LEFT JOIN
SARTRFO AS SARTRFO2 -- Vue
ON (SARTTFO.REF = SARTRFO2.REF
	AND SARTTFO.SREF = SARTRFO2.SREF
	AND SARTRFO2.ETB = '');

Enfin, j'ai encapsulé cette requête comme sous-requête dans le FROM d'une requête principale afin de pouvoir ajouter une clause WHERE et ORDER BY :

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
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
 
SELECT
Z.REF,
Z.SREF,
Z.ETB,
Z.QTE,
Z.CR,
Z.COECOD,
Z.PA,
Z.POSITION
 
FROM
(
	SELECT
	SARTRFO.REF,
	SARTRFO.SREF,
	SARTRFO.ETB,
	COALESCE(SARTTFO.QTE, SARTTFO2.QTE) AS QTE,
	SARTRFO.CR,
	SARTRFO.COECOD,
	COALESCE(SARTTFO.PA, SARTTFO2.PA) AS PA,
	COALESCE(SARTTFO.POSITION, SARTTFO2.POSITION) AS POSITION
 
	FROM
	SARTRFO -- Vue
 
	LEFT JOIN
	SARTTFO -- Vue
	ON (SARTTFO.REF = SARTRFO.REF
		AND SARTTFO.SREF = SARTRFO.SREF
		AND SARTTFO.ETB = SARTRFO.ETB)
 
	LEFT JOIN
	SARTTFO AS SARTTFO2 -- Vue
	ON (SARTTFO2.REF = SARTRFO.REF
		AND SARTTFO2.SREF = SARTRFO.SREF
		AND SARTTFO2.ETB = '')
 
	UNION
 
	SELECT
	SARTTFO.REF,
	SARTTFO.SREF,
	SARTTFO.ETB,
	SARTTFO.QTE,
	SARTTFO.CR,
	COALESCE(SARTRFO.COECOD, SARTRFO2.COECOD) AS COECOD,
	SARTTFO.PA,
	SARTTFO.POSITION
 
	FROM
	SARTTFO -- Vue
 
	LEFT JOIN
	SARTRFO -- Vue
	ON (SARTTFO.REF = SARTRFO.REF
		AND SARTTFO.SREF = SARTRFO.SREF
		AND SARTTFO.ETB = SARTRFO.ETB)
 
	LEFT JOIN
	SARTRFO AS SARTRFO2 -- Vue
	ON (SARTTFO.REF = SARTRFO2.REF
		AND SARTTFO.SREF = SARTRFO2.SREF
		AND SARTRFO2.ETB = '')
) AS Z
 
WHERE
Z.REF = 'E'
AND Z.POSITION = 1
 
ORDER BY
Z.REF,
Z.SREF,
Z.ETB,
Z.QTE;

Et boum ça fait des "Nom d'une marque de céréale censurée" !
J'aurais aussi pu utiliser des CTE. Et franchement, c'est même une meilleure idée puisque mes vues ne sont destinées qu'à être utilisées dans cette requête (à l'exception peut-être de TFODESC qui peut servir dans d'autres requêtes).

Je constate d'ailleurs que le résultat obtenu est meilleur que celui que j'attendais car il y a des lignes fausses dans ce que j'avais imaginé comme résultat et qui dépendent pour la plupart de la priorité qu'on donne à chaque colonne.
Typiquement, dans la situation où je commande 30 unités de la sous-référence SR1 de l'article E, dois-je utiliser :
- le tarif valable pour toutes les sous-références de E avec une quantité seuil de 30
- le tarif valable pour n'importe quelle quantité de la sous-référence SR1 uniquement
Dans le 1er cas, c'est la QTE qui a la priorité, dans le second, c'est SREF. J'imagine que les deux sont codables. Mais je me suis plié au fonctionnement de l'ERP qui exploite cette DB avec l'ordre de priorité suivant : SREF -> ETB -> QTE.
De ce fait, dans ce cas, c'est le tarif valable pour n'importe quelle quantité de la sous-référence SR1 uniquement qui prime sur l'autre.

J'attends de m'être assuré du bon fonctionnement de mon code, notamment sur la DB de prod, pour le tagé en résolu.

Bien à vous,