Requête SELECT complexe

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:

---

1 2 3 4 5 6

DROP DATABASE IF EXISTS DBTEST;   CREATE DATABASE DBTEST CHARACTER SET 'utf8';   USE DBTEST;

---

Création tables

Code:

---

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:

---

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:

---

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:

---

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.

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:

---

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:

---

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:

---

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:

---

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:

---

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,