HELP REQUÊTE SQL

Version imprimable

5 pièce(s) jointe(s)

HELP REQUÊTE SQL

Bonjour,

J'ai la requête suivante qui me pose problème :
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 SELECT DISTINCT OFS.ID_OFS as NUMERO_OF, S_CMD.DATE_CMD as DATE_CDE, S_CMD.ID_S_CMD as N_COMMANDE, S_CMD.ID_ANNEE as ANNEE, S_CMD_LG.N_LIGNE as LIGNE, FOURN_ST.NOM as FOURNISSEUR, ARTICLE.REF as ARTICLE, ARTICLE.LIBELLE as DESIGNATION, S_CMD_LG.QTE_CMDEE as QTE_CMDEE, S_BL.N_BL as BL_ST, S_BL_LG.QTE_LIVREE as QTE_BL_ST FROM S_CMD INNER JOIN S_CMD_LG ON S_CMD.CD_S_CMD = S_CMD_LG.CD_S_CMD INNER JOIN FOURN_ST ON FOURN_ST.CD_FOURN_ST = S_CMD.CD_FOURN_ST INNER JOIN OFS ON OFS.CD_OFS = S_CMD_LG.CD_OFS INNER JOIN ARTICLE ON ARTICLE.CD_ARTICLE = OFS.CD_ARTICLE FULL JOIN S_BL_LG ON S_CMD_LG.CD_S_CMD = S_BL_LG.CD_S_CMD AND S_CMD_LG.N_LIGNE = S_BL_LG.N_LIGNE FULL JOIN S_BL ON S_BL_LG.CD_S_BL = S_BL.CD_S_BL WHERE S_CMD_LG.QTE_CMDEE <> 0 AND S_CMD.DATE_CMD > add_months(sysdate, -24) ORDER BY S_CMD.DATE_CMD asc
Exemple de résultat via cette requête :

Pièce jointe 586537

Je ne comprends pas certains de mes doublons d'OF (Ordre de Fabrication : colonne A), si je prends l'exemple de l'OF 21928, je le comprends car j'ai eu 2 bons de livraison pour livrer cette commande (BL de 4 + BL de 24 : colonne K).

Par contre, je ne comprends le doublon de l'OF 21929, il s'agit d'un OF de 28 pièces mais seule une commande (n°357) a été passée pour 24 pièces sur cet OF (commande livrée avec un BL n° 91201 de 24 pièces), je ne comprends donc pas pourquoi la ligne 703 apparait. Je ne comprends pas son apparition car il n'existe pas de commande 357 ligne 1 avec 28 pièces commandées sans aucune livraison...

Je ne sais pas comment faire pour faire disparaitre la ligne 703. J'ai d'abord pensé changé mes jointures pour ne garder que les commandes AVEC forcement au moins 1 BL mais cette jointure m'exclue les cas de figures suivants (que je souhaite garder) :

Pièce jointe 586538

Ici, on passe commande de 3 pièces mais nous ne serons jamais livrés donc pas de BL_ST (Bon de Livraison Sous-Traitant) ni de S_BL_LG.QTE_LIVREE.

Pour résumer : avez-vous une idée de correction de ma requête pour ne pas faire apparaitre la ligne 703 de ma première photo sans faire disparaitre la ligne 7222 de ma deuxième photo ?

PS : table S_CMD ici Pièce jointe 586535

Cordialement,

Marin SIMONS

13/12/2020, 21h01
vanagreg

Bonjour,

Peut-être utiliser des LEFT OUTER JOIN plutôt que FULL OUTER JOIN.
13/12/2020, 21h28
marin95600

Bonsoir,

Non, quand je remplace mes 2 FULL JOIN par des LEFT, rien ne change. Par contre quand je les change avec des RIGHT, la ligne 703 disparait mais la 7222 aussi...

Cordialement,

Marin SIMONS
13/12/2020, 23h33
Paraffine

Je ne comprends pas comment tu pourrais avoir deux valeurs différentes (24 et 28) de QTE_CMDEE (colonne de S_CMD_LG) pour une "même" commande et une même ligne de commande ? 8O
Peux-tu ajouter à ta requête la colonne CD_S_CMD ?

Bonjour,

Apparemment c'est un problème d'avenant (plusieurs avenants possibles pour une seule commande). Je cherche donc désormais à afficher seulement le dernier avenant (MAX).

J'ai essayé ça mais ça ne marche pas :

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
SELECT DISTINCT
 
 OFS.ID_OFS as NUMERO_OF,
 S_CMD.DATE_CMD as DATE_CDE,
 S_CMD.ID_S_CMD as N_COMMANDE,
 S_CMD.ID_ANNEE as ANNEE,
 S_CMD_LG.N_LIGNE as LIGNE,
 FOURN_ST.NOM as FOURNISSEUR,
 ARTICLE.REF as ARTICLE,
 ARTICLE.LIBELLE as DESIGNATION,
 S_CMD_LG.QTE_CMDEE as QTE_CMDEE,
 S_BL.N_BL as BL_ST,
 MAX(S_CMD.N_AVENANT) as N_AVENANT,
 S_BL_LG.QTE_LIVREE as QTE_BL_ST
 
  FROM S_CMD 
 
 INNER JOIN S_CMD_LG ON S_CMD.CD_S_CMD = S_CMD_LG.CD_S_CMD
 INNER JOIN FOURN_ST ON FOURN_ST.CD_FOURN_ST = S_CMD.CD_FOURN_ST 
 INNER JOIN OFS ON OFS.CD_OFS = S_CMD_LG.CD_OFS
 INNER JOIN ARTICLE ON ARTICLE.CD_ARTICLE = OFS.CD_ARTICLE
 FULL JOIN S_BL_LG ON S_CMD_LG.CD_S_CMD = S_BL_LG.CD_S_CMD AND S_CMD_LG.N_LIGNE = S_BL_LG.N_LIGNE
 FULL JOIN S_BL ON S_BL_LG.CD_S_BL = S_BL.CD_S_BL
 
 WHERE S_CMD_LG.QTE_CMDEE <> 0
 AND S_CMD.DATE_CMD > add_months(sysdate, -24)
 
ORDER BY S_CMD.DATE_CMD asc
GROUP BY S_BL_LG.QTE_LIVREE, S_BL.N_BL, S_CMD_LG.QTE_CMDEE, ARTICLE.LIBELLE, ARTICLE.REF, FOURN_ST.NOM, S_CMD_LG.N_LIGNE, S_CMD.ID_ANNEE, S_CMD.ID_S_CMD, S_CMD.DATE_CMD, OFS.ID_OFS;

Une idée ?

Cordialement,

Marin SIMONS

14/12/2020, 10h50
skuatamad

Quelle est la clé primaire de la table S_CMD ?

Commencez par écrire une requête permettant de trouver le dernier avenant de chaque commande (MAX ou row_number), puis utilisez cette requête en CTE (WITH) pour joindre vers les autres tables.
14/12/2020, 11h02
marin95600

1 pièce(s) jointe(s)

Bonjour,

J'imagine que la clé primaire est celle surlignée ? Donc CD_S_CMD ?

Pièce jointe 586568

Pouvez-vous détailler l'utilisation de la fonction WITH ?

Le fait d'utiliser

Code:

MAX(S_CMD.N_AVENANT) as N_AVENANT,

ne suffit pas ?

Cordialement,

Marin SIMONS

Comme dit skuatamad :

Citation:

Commencez par écrire une requête permettant de trouver le dernier avenant de chaque commande (MAX ou row_number)

La première chose est de savoir comment tu IDENTIFIES une même commande 'logique' avec ses avenants successifs.

En imaginant que c'est sur (ID_S_CMD, ID_ANNEE) avec MAX(Avenant) pour avoir le dernier en date - mais cela je n'en sait rien et cela dépend de ton application...

Avec des sous-requêtes ça eût donné:

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
SELECT DISTINCT
 
 OFS.ID_OFS as NUMERO_OF,
 S_CMD.DATE_CMD as DATE_CDE,
 S_CMD.ID_S_CMD as N_COMMANDE,
 S_CMD.ID_ANNEE as ANNEE,
 S_CMD_LG.N_LIGNE as LIGNE,
 FOURN_ST.NOM as FOURNISSEUR,
 ARTICLE.REF as ARTICLE,
 ARTICLE.LIBELLE as DESIGNATION,
 S_CMD_LG.QTE_CMDEE as QTE_CMDEE,
 S_BL.N_BL as BL_ST,
 MAX(S_CMD.N_AVENANT) as N_AVENANT,
 S_BL_LG.QTE_LIVREE as QTE_BL_ST
 
  FROM S_CMD 
 
 INNER JOIN S_CMD_LG ON S_CMD.CD_S_CMD = S_CMD_LG.CD_S_CMD
 INNER JOIN FOURN_ST ON FOURN_ST.CD_FOURN_ST = S_CMD.CD_FOURN_ST 
 INNER JOIN OFS ON OFS.CD_OFS = S_CMD_LG.CD_OFS
 INNER JOIN ARTICLE ON ARTICLE.CD_ARTICLE = OFS.CD_ARTICLE
 LEFT JOIN S_BL_LG ON S_CMD_LG.CD_S_CMD = S_BL_LG.CD_S_CMD AND S_CMD_LG.N_LIGNE = S_BL_LG.N_LIGNE
 LEFT JOIN S_BL ON S_BL_LG.CD_S_BL = S_BL.CD_S_BL
 
 WHERE S_CMD_LG.QTE_CMDEE <> 0
 AND S_CMD.DATE_CMD > add_months(sysdate, -24)
 AND S_CMD.AVENANT = (select Max(CMD2.AVENANT)
					    from S_CMD CMD2
                       where CMD2.ID_S_CMD = S_CMD.ID_S_CMD
                         and CMD2.ID_ANNEE= S_CMD.ID_ANNEE
					   )
ORDER BY S_CMD.DATE_CMD asc
GROUP BY S_BL_LG.QTE_LIVREE, S_BL.N_BL, S_CMD_LG.QTE_CMDEE, ARTICLE.LIBELLE, ARTICLE.REF, FOURN_ST.NOM, S_CMD_LG.N_LIGNE, S_CMD.ID_ANNEE, S_CMD.ID_S_CMD, S_CMD.DATE_CMD, OFS.ID_OFS;

Avec WITH, on définit comme une "table" temporaire contenant ce qui nous intéresse, le dernier avenant (toujours sous réserve de "comment j'identifie que j'ai affaire à la même commande, et comment je trouve le dernier avenant en date"):

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
WITH 
   DER_CMD (AVENANT, ID_S_CMD, ID_ANNEE)
   AS 
     (SELECT MAX(AVENANT), ID_S_CMD, ID_ANNEE
      FROM   S_CMD
      GROUP  BY ID_S_CMD, ID_ANNEE)
 
SELECT DISTINCT
 
 OFS.ID_OFS as NUMERO_OF,
 S_CMD.DATE_CMD as DATE_CDE,
 S_CMD.ID_S_CMD as N_COMMANDE,
 S_CMD.ID_ANNEE as ANNEE,
 S_CMD_LG.N_LIGNE as LIGNE,
 FOURN_ST.NOM as FOURNISSEUR,
 ARTICLE.REF as ARTICLE,
 ARTICLE.LIBELLE as DESIGNATION,
 S_CMD_LG.QTE_CMDEE as QTE_CMDEE,
 S_BL.N_BL as BL_ST,
 MAX(S_CMD.N_AVENANT) as N_AVENANT,
 S_BL_LG.QTE_LIVREE as QTE_BL_ST
 
  FROM S_CMD 
 INNER JOIN DER_CMD ON S_CMD.ID_S_CMD = DER_CMD.ID_S_CMD AND S_CMD.ID_ANNEE = DER_CMD.ID_ANNEE AND S_CMD.AVENANT = DER_CMD.AVENANT
 INNER JOIN S_CMD_LG ON S_CMD.CD_S_CMD = S_CMD_LG.CD_S_CMD
 INNER JOIN FOURN_ST ON FOURN_ST.CD_FOURN_ST = S_CMD.CD_FOURN_ST 
 INNER JOIN OFS ON OFS.CD_OFS = S_CMD_LG.CD_OFS
 INNER JOIN ARTICLE ON ARTICLE.CD_ARTICLE = OFS.CD_ARTICLE
 LEFT JOIN S_BL_LG ON S_CMD_LG.CD_S_CMD = S_BL_LG.CD_S_CMD AND S_CMD_LG.N_LIGNE = S_BL_LG.N_LIGNE
 LEFT JOIN S_BL ON S_BL_LG.CD_S_BL = S_BL.CD_S_BL
 
 WHERE S_CMD_LG.QTE_CMDEE <> 0
 AND S_CMD.DATE_CMD > add_months(sysdate, -24)
 
ORDER BY S_CMD.DATE_CMD asc
GROUP BY S_BL_LG.QTE_LIVREE, S_BL.N_BL, S_CMD_LG.QTE_CMDEE, ARTICLE.LIBELLE, ARTICLE.REF, FOURN_ST.NOM, S_CMD_LG.N_LIGNE, S_CMD.ID_ANNEE, S_CMD.ID_S_CMD, S_CMD.DATE_CMD, OFS.ID_OFS;

1 pièce(s) jointe(s)

Bonjour,

Je ne parviens pas à faire fonctionner le code suivant :

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
WITH 
   DER_CMD (N_AVENANT, ID_S_CMD, ID_ANNEE)
   AS 
     (SELECT MAX(N_AVENANT), ID_S_CMD, ID_ANNEE
      FROM   S_CMD
      GROUP  BY ID_S_CMD, ID_ANNEE)
 
SELECT DISTINCT
 
 OFS.ID_OFS as NUMERO_OF,
 S_CMD.DATE_CMD as DATE_CDE,
 S_CMD.ID_S_CMD as N_COMMANDE,
 S_CMD.ID_ANNEE as ANNEE,
 S_CMD_LG.N_LIGNE as LIGNE,
 FOURN_ST.NOM as FOURNISSEUR,
 ARTICLE.REF as ARTICLE,
 ARTICLE.LIBELLE as DESIGNATION,
 S_CMD_LG.QTE_CMDEE as QTE_CMDEE,
 S_BL.N_BL as BL_ST,
 MAX(S_CMD.N_AVENANT) as N_AVENANT,
 S_BL_LG.QTE_LIVREE as QTE_BL_ST
 
  FROM S_CMD 
 INNER JOIN DER_CMD ON S_CMD.ID_S_CMD = DER_CMD.ID_S_CMD AND S_CMD.ID_ANNEE = DER_CMD.ID_ANNEE AND S_CMD.AVENANT = DER_CMD.AVENANT
 INNER JOIN S_CMD_LG ON S_CMD.CD_S_CMD = S_CMD_LG.CD_S_CMD
 INNER JOIN FOURN_ST ON FOURN_ST.CD_FOURN_ST = S_CMD.CD_FOURN_ST 
 INNER JOIN OFS ON OFS.CD_OFS = S_CMD_LG.CD_OFS
 INNER JOIN ARTICLE ON ARTICLE.CD_ARTICLE = OFS.CD_ARTICLE
 FULL JOIN S_BL_LG ON S_CMD_LG.CD_S_CMD = S_BL_LG.CD_S_CMD AND S_CMD_LG.N_LIGNE = S_BL_LG.N_LIGNE
 FULL JOIN S_BL ON S_BL_LG.CD_S_BL = S_BL.CD_S_BL
 
 WHERE S_CMD_LG.QTE_CMDEE <> 0
 AND S_CMD.DATE_CMD > add_months(sysdate, -24)
 
ORDER BY S_CMD.DATE_CMD asc

J'ai le message d'erreur suivant :

Pièce jointe 586582

Cordialement,

Marin SIMONS

Bonjour à tous,

Le code suivant fonctionne :

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
WITH 
   DER_CMD (N_AVENANT, ID_S_CMD, ID_ANNEE)
   AS 
     (SELECT MAX(N_AVENANT), ID_S_CMD, ID_ANNEE
      FROM   S_CMD
      GROUP  BY ID_S_CMD, ID_ANNEE)
 
SELECT DISTINCT
 
 OFS.ID_OFS as NUMERO_OF,
 S_CMD.DATE_CMD as DATE_CDE,
 S_CMD.ID_S_CMD as N_COMMANDE,
 S_CMD.ID_ANNEE as ANNEE,
 S_CMD_LG.N_LIGNE as LIGNE,
 FOURN_ST.NOM as FOURNISSEUR,
 ARTICLE.REF as ARTICLE,
 ARTICLE.LIBELLE as DESIGNATION,
 S_CMD_LG.QTE_CMDEE as QTE_CMDEE,
 S_BL.N_BL as BL_ST,
 MAX(S_CMD.N_AVENANT) as N_AVENANT,
 S_BL_LG.QTE_LIVREE as QTE_BL_ST
 
  FROM S_CMD 
 
 INNER JOIN DER_CMD ON S_CMD.ID_S_CMD = DER_CMD.ID_S_CMD AND S_CMD.ID_ANNEE = DER_CMD.ID_ANNEE AND S_CMD.N_AVENANT = DER_CMD.N_AVENANT
 INNER JOIN S_CMD_LG ON S_CMD.CD_S_CMD = S_CMD_LG.CD_S_CMD
 INNER JOIN FOURN_ST ON FOURN_ST.CD_FOURN_ST = S_CMD.CD_FOURN_ST 
 INNER JOIN OFS ON OFS.CD_OFS = S_CMD_LG.CD_OFS
 INNER JOIN ARTICLE ON ARTICLE.CD_ARTICLE = OFS.CD_ARTICLE
 FULL JOIN S_BL_LG ON S_CMD_LG.CD_S_CMD = S_BL_LG.CD_S_CMD AND S_CMD_LG.N_LIGNE = S_BL_LG.N_LIGNE
 FULL JOIN S_BL ON S_BL_LG.CD_S_BL = S_BL.CD_S_BL
 
 WHERE S_CMD_LG.QTE_CMDEE <> 0
 AND S_CMD.DATE_CMD > add_months(sysdate, -100)
 
GROUP BY S_BL_LG.QTE_LIVREE, S_BL.N_BL, S_CMD_LG.QTE_CMDEE, ARTICLE.LIBELLE, ARTICLE.REF, FOURN_ST.NOM, S_CMD_LG.N_LIGNE, S_CMD.ID_ANNEE, S_CMD.ID_S_CMD, S_CMD.DATE_CMD, OFS.ID_OFS
ORDER BY S_CMD.DATE_CMD asc

Est-il optimisé ?

Cordialement,

Marin SIMONS

14/12/2020, 14h16
Paraffine

En premier lieu : as-tu validé que ( ID_ANNEE, ID_S_CMD) est bien l'identifiant naturel de la commande "logique" ?

Performance :
As-tu noté le temps d'exécution de la requête ?

Étant donné que le FULL JOIN ne te sert à rien ici, tu peux passer sans regret au LEFT JOIN.

Si le temps d'exécution reste trop élevé : as-tu dans la table S_CMD un index sur la colonne (DATE_CMD), ainsi que sur ( ID_ANNEE, ID_S_CMD, N_AVENANT) ?

Les statistiques sont-elles à jour ?

Après il reste le stade EXPLAIN PLAN.
14/12/2020, 14h39
marin95600

Bonjour,

La commande logique a bien un numéro (ID_S_CMD) + une année (ID_ANNEE). Ensuite, elle peut avoir plusieurs avenants.
Pour moi ça me parait cohérent. Le temps d'actualisation est très rapide.

Je vais en rester là, merci pour votre aide.

Cordialement,

Marin SIMONS