Requête qui donne la quantité reste à livrer [Résolu]

[tunis71187]

* Bonjour, *

J'ai un problème avec une requête SQL qui doit me retourner la quantité reste à livrer pour une commande bien déterminée.
La solution est :
1- Une requête qui retourne les articles+ quantité reste à livrer pour une commande,

UNION

2- Une requête qui retourne les articles+ quantité pas encore livrés

Après le test ça ne répond plus au besoin.

Les tables sont:

1) Commande(num_commande, date_commande, #ref_fournisseur)
2) Article(reference, designation)
3) CDE_ART(num_commande,reference,quantite)
4) LIVRAISON(code_livraison, date_liv, # n_comm)
5)LIV_ART(code_livraison, reference, quantite_livree, q_rest_liv)

La requête utilisée est la suivante:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT t.reference, t.qrl
FROM livraison l, liv_art t
WHERE l.num_commande = 4
AND l.code_livraison = t.code_livraison
AND (SELECT c.quantite
FROM cde_art c
WHERE c.reference = t.reference
AND c.num_commande = 4) - nvl(t.quantite_livree,0) > 0
 
union
 
-- Les articles pas encore livrés
 
SELECT c.reference, c.quantite
FROM cde_art c, article a
WHERE c.reference = a.reference
AND c.num_commande = 4
AND c.reference NOT IN (SELECT a.reference
                       FROM livraison l, liv_art a
                       WHERE l.num_commande = 4
                       AND l.code_livraison = a.code_livraison)

S'il vous plait des propositions c'est très urgent. Merci

[CinePhil]

La solution est :
1- Une requête qui retourne les articles+ quantité reste à livrer pour une commande,

UNION

2- Une requête qui retourne les articles+ quantité pas encore livrés

Euh... tes phrases 1 et 2 ont la même signification !
"quantité reste à livrer" et "quantité pas encore livrée", dans mon français, ça veut dire la même chose !

quantité reste à livrée = quantité commandée - quantité livrée

Ce n'est pas avec une requête union que tu vas obtenir ce résultat !

Code :

1
2
3
4
5
6
7
SELECT ca.reference, 
    ca.quantite - COALESCE(SUM(la.quantite_livree), 0) AS reste_a_livrer
FROM CDE_ART ca
LEFT OUTER JOIN LIVRAISON l ON l.n_comm = ca.num_commande
    LEFT OUTER JOIN LIV_ART la ON la.code_livraison = l.code_livraison
WHERE ca.num_commande = 4
GROUP BY ca_reference, ca.quantite

Remarques sur ta structure de BDD et sur ta requête :
1) num_commande, reference et code_livraison, s'il sont de type alphanumérique sont de mauvaises clés pour les tables. Idem pour ref_fournisseur.

2) q_rest_liv ne devrait pas figurer dans LIV_ART puisque ça peut être calculé.

3) La syntaxe normalisée depuis 1992 pour les jointures utilise l'opérateur JOIN.

[tunis71187]

Bonjour;
la 1) c'est pour retourner les articles livrées mais pas complet et 2) c'est pour retourner les articles ne figurant pas dans la table liv_art.

comme vous disez le num_commande et reference sont des alphanumériques.

Votre solution me donne juste -quantité (commandée). le problème dans ma requête est qu'elle retourne même une quantité = à 0 or je veux juste les articles qui ont un manque de quantité et cela pour une commande bien déterminée. ( un article peut être livrer deux fois par exemple : aujourd'hui 3 et demain 5 pour une quantité commandée de 8).

[CinePhil]

Envoyé par tunis71187

Votre solution me donne juste -quantité (commandée).

Voilà encore une phrase qui manque de sens !

Ma requête est sensée donnée pour chaque référence d'article de la commande numéro 4 la quantité commandée moins la somme des quantités livrées, c'est à dire le reste à livrer de chaque article de la commande 4.

je veux juste les articles qui ont un manque de quantité

Ajoutons une contrainte sur le regroupement :

Code :

1
2
3
4
5
6
7
8
SELECT ca.reference, 
    ca.quantite - COALESCE(SUM(la.quantite_livree), 0) AS reste_a_livrer
FROM CDE_ART ca
LEFT OUTER JOIN LIVRAISON l ON l.n_comm = ca.num_commande
    LEFT OUTER JOIN LIV_ART la ON la.code_livraison = l.code_livraison
WHERE ca.num_commande = 4
GROUP BY ca_reference, ca.quantite
HAVING ca.quantite - COALESCE(SUM(la.quantite_livree), 0) > 0

[tunis71187]

Merci pour vos réponses,
Prendre cet exemple s'il vous plait et vous pouvez me comprendre:
num_commande= 1, contient 2 articles de reference 1 et 2 et ayant les quantités commandées suivantes 20 et 10.
le premier bon de livraison: reference 1, quantité_livrée 15 reste 5
reference 2, quantité_livrée 3 reste 7,
votre requête donne :

reference 1, reste_a_livrer 2 !!!!!

[CinePhil]

Effectivement, j'étais allé un peu trop vite à cause de la structure de la BDD qui n'est pas très normale.

Qu'est-ce qui a été commandé sur la commande 1 :

Code :

1
2
3
4
SELECT ca.reference, 
  ca.quantite AS qte_commandee
FROM CDE_ART ca
WHERE ca.num_commande = 1

Qu'est-ce qui a été livré sur la commande 1 :

Code :

1
2
3
4
SELECT la.reference, la.quantite_livree
FROM LIV_ART la
INNER JOIN LIVRAISON l ON l.code_livraison = la.code_livraison
WHERE l.n_comm = 1

On a la référence des articles dans ces deux requêtes donc on peut les joindre afin de ne pas multiplier les lignes :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT t1.reference, t1.qte_commandee, t2.quantite_livree
FROM 
(
    SELECT ca.reference, 
        ca.quantite AS qte_commandee
    FROM CDE_ART ca
    WHERE ca.num_commande = 1
) t1
INNER JOIN
(
    SELECT la.reference, la.quantite_livree
    FROM LIV_ART la
    INNER JOIN LIVRAISON l ON l.code_livraison = la.code_livraison
    WHERE l.n_comm = 1
) t2 ON t2.reference = t1.reference

Comme il peut y avoir plusieurs livraisons pour une commande, on peut regrouper et additionner les quantités livrées et soustraire cette somme de la quantité commandée, comme dans ma requête précédente :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT t1.reference, t1.qte_commandee - SUM(t2.quantite_livree) AS reste_a_livrer
FROM 
(
    SELECT ca.reference, 
        ca.quantite AS qte_commandee
    FROM CDE_ART ca
    WHERE ca.num_commande = 1
) t1
LEFT OUTER JOIN
(
    SELECT la.reference, la.quantite_livree
    FROM LIV_ART la
    INNER JOIN LIVRAISON l ON l.code_livraison = la.code_livraison
    WHERE l.n_comm = 1
) t2 ON t2.reference = t1.reference
GROUP BY t1.reference, t1.qte_commandee
HAVING t1.qte_commandee - SUM(t2.quantite_livree) > 0

[tunis71187]

Merci Mr. CinePhil,
ça marche parfaitement, mais pour avoir une résultat complète il faut ajouter ma deuxième instruction UNION pour afficher même les articles non encore livrés.

Autre demande si ne vous dérange plus, je veux afficher pour une commande son fournisseur(libelle_fournisseur).

[CinePhil]

Envoyé par tunis71187

mais pour avoir une résultat complète il faut ajouter ma deuxième instruction UNION pour afficher même les articles non encore livrés.

Tu y tiens à ton UNION toi ! Mariage en vue ?
Avec le LEFT JOIN, tu devrais avoir tous les articles commandés, même ceux qui n'ont pas été commencés de livrer. Là où ça peut coincer (je n'ai pas testé), c'est que dans ce cas là, SUM(t2.quantite_livree) sera à NULL et l'opération retournera probablement NULL également. Il faut alors utiliser COALESCE pour contrer ce NULL.

Code :

SELECT t1.reference, t1.qte_commandee - COALESCE(SUM(t2.quantite_livree), 0) AS reste_a_livrer

Et si tu veux vraiment afficher le fait qu'une commande est complètement à livrer ou a du reste à livrer, tu peux :
1) Manière simple à traiter par le logiciel qui lance la requête et affiche le résultat
Ajouter la quantité commandée en colonne du SELECT et le logiciel se charge de comparer le reste à livrer à la quantité commandée :

Code :

1
2
SELECT t1.reference, t1.qte_commandee, 
  t1.qte_commandee - COALESCE(SUM(t2.quantite_livree), 0) AS reste_a_livrer

2) Méthode plus complexe faite par le SGBD
Utiliser un CASE et faire afficher en clair par le SGBD l'état de la ligne de commande

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
SELECT tmp.reference, tmp.qte_commandee,
  CASE
    WHEN tmp.reste_a_alivrer = tmp.qte_commandee THEN 'TOUT'
    ELSE tmp.reste_a_livrer
  END AS A_livrer
FROM
(
  SELECT t1.reference, t1.qte_commandee, 
    t1.qte_commandee - COALESCE(SUM(t2.quantite_livree), 0) AS reste_a_livrer
  FROM 
  (
      SELECT ca.reference, 
      ca.quantite AS qte_commandee
      FROM CDE_ART ca
      WHERE ca.num_commande = 1
  ) t1
  LEFT OUTER JOIN
  (
      SELECT la.reference, la.quantite_livree
      FROM LIV_ART la
      INNER JOIN LIVRAISON l ON l.code_livraison = la.code_livraison
      WHERE l.n_comm = 1
  ) t2 ON t2.reference = t1.reference
  GROUP BY t1.reference, t1.qte_commandee
  HAVING t1.qte_commandee - SUM(t2.quantite_livree) > 0
) tmp

Autre demande si ne vous dérange plus, je veux afficher pour une commande son fournisseur(libelle_fournisseur).

Pas dur !
Tu ajoutes la colonne ref_fournisseur dans le SELECT et tu fais une jointure avec la table commande dans la première sous-requête :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT t1.ref_fournisseur, t1.reference, t1.qte_commandee, 
    t1.qte_commandee - COALESCE(SUM(t2.quantite_livree), 0) AS reste_a_livrer
  FROM 
  (
      SELECT ca.reference, 
      ca.quantite AS qte_commandee
      FROM CDE_ART ca
      INNER JOIN commande c ON c.num_commande = ca.num_commande
      WHERE ca.num_commande = 1
  ) t1
  LEFT OUTER JOIN
  (
      SELECT la.reference, la.quantite_livree
      FROM LIV_ART la
      INNER JOIN LIVRAISON l ON l.code_livraison = la.code_livraison
      WHERE l.n_comm = 1
  ) t2 ON t2.reference = t1.reference
  GROUP BY t1.reference, t1.qte_commandee
  HAVING t1.qte_commandee - SUM(t2.quantite_livree) > 0

[tunis71187]

Bonjour Mr. CinePhil,
Excusez moi si je vous dérange.
Votre dernière requête je l'ai testé sous PLSQL developer et il m'affiche un erreur à propos le ref_fournisseur.
Je suis coincé vraiment.
Pour plus de clarté:
je vais utiliser cette requête sous Report Builder pour m'afficher un état, donc j'ai besoin du numéro de commande, date de la commande et le fournisseur de cette commande, les references et les désignations des articles non complètement livrés y compris les articles non encore livrés ( je dis ça car j'ai testé avec votre requête et ça m'affiche que ceux qui ont une quantité restante à livrer).

Excusez moi de nouveau. Merci

[CinePhil]

Envoyé par tunis71187

Votre dernière requête je l'ai testé sous PLSQL developer

C'est Oracle ça non ?

et il m'affiche un erreur à propos le ref_fournisseur.

Et ma boule de cristal va me donner le texte de l'erreur ?
Tu as essayé de comprendre le sens de cette erreur ?

je vais utiliser cette requête sous Report Builder pour m'afficher un état,

Connais pas ! On sort du pur domaine SQL là !

j'ai testé avec votre requête

Laquelle ?

et ça m'affiche que ceux qui ont une quantité restante à livrer.

On commence à tourner en rond là !
Conformément aux règles du forum, il faudrait la description exacte des tables, un petit jeu de données et le résultat attendu.

[tunis71187]

Encore pardon.

Je dois répéter la structure de ma BD:
1) Fournisseur(reference_four, libelle_four)
2) Commande(num_commande, date_commande, #reference_four)
3) Article(reference, designation)
4) CDE_ART(num_commande,reference,quantite)
5) LIVRAISON(code_livraison, date_livraison, #num_commande)
6) LIV_ART(code_livraison, reference, quantite_livree, q_rest_liv).

J'ai besoin de :
1) numéro de commande, date de la commande,
2) le fournisseur de cette commande,
3) les références et les désignations des articles non complètement livrés y compris les articles non encore livrés.
4) enfin pour chaque article, la quantité commandée et la quantité reste à livrer.

La dernière requête où vous utilisez le ref_fournisseur.

Comme jeu de test si vous voulez on va suivre cet exemple:

num_commande= 1, contient 2 articles de référence 1 et 2 et ayant les quantités commandées suivantes 20 et 10.
le premier bon de livraison contient : référence 1, quantité_livrée 15 référence 2, quantité_livrée 3.
on va tout d'abord ajouter seulement le premier article dans la base et voir le résultat.

Excusez moi s'il vous plait je suis coincé.

[CinePhil]

Tu cherches de ton côté quand même ? Je ne vais pas faire tout ton boulot non plus !
C'est quoi le message d'erreur à propos de ref_fournisseur ?

Et quand je demandais la description exacte, je parlais des ordres CREATE TABLE.

Quand tu écris la colonne #ref_fournisseur, le # fait partie du nom ou, comme c'est souvent le cas, est juste là pour indiquer que c'est une clé étrangère ?

Pas trop le temps de regarder ton truc maintenant.

[tunis71187]

Merci pour votre réponse, je cherche l'aide pas de faire tout mon boulot non plus.
Message d'erreur:

ORA-00904: "T1.ref_fournisseur" : identificateur non valide.

Même je mis le nom correct situé dans ma base.
Description exacte:

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
CREATE TABLE FOURNISSEUR
(
  REFERENCE_FOUR     VARCHAR2(50) NOT NULL,
  LIBELLE_FOUR       VARCHAR2(100)
)
CREATE TABLE COMMANDE
(
  NUM_COMMANDE   VARCHAR2(50) NOT NULL,
  REFERENCE_FOUR VARCHAR2(50),
  DATE_COMMANDE  DATE
)
CREATE TABLE ARTICLE
(
  REFERENCE    VARCHAR2(50) NOT NULL,
  DESIGNATION  VARCHAR2(150)
)
CREATE TABLE CDE_ART
(
  NUM_COMMANDE  VARCHAR2(50) NOT NULL,
  REFERENCE     VARCHAR2(50) NOT NULL,
  QUANTITE      NUMBER
)
CREATE TABLE LIVRAISON
(
  CODE_LIVRAISON VARCHAR2(50) NOT NULL,
  NUM_COMMANDE   VARCHAR2(50),
  DATE_LIVRAISON DATE
)
CREATE TABLE LIV_ART
(
  CODE_LIVRAISON  VARCHAR2(50) NOT NULL,
  REFERENCE       VARCHAR2(50) NOT NULL,
  QUANTITE_LIVREE NUMBER,
  QRL             NUMBER -- Quantité reste à livrer
)

Les # pour les clés étrangère.

Juste je demande l'aide.

[CinePhil]

Envoyé par tunis71187

Message d'erreur:

Même je mis le nom correct situé dans ma base.

Apparemment pas !
Description exacte:

Code :

1
2
3
4
5
6
7
create table COMMANDE
(
  NUM_COMMANDE   VARCHAR2(50) not null,
  REFERENCE_FOUR VARCHAR2(50),
  DATE_COMMANDE  DATE
)

Tu vois la différence ?

[tunis71187]

Bonjour Mr. CinePhil,

J'ai utilisé la requête suivante qui donne parfaitement la quantité reste à livrer:

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
SELECT t1.reference, t1.qte_commandee, 
    t1.qte_commandee - COALESCE(SUM(t2.quantite_livree), 0) AS reste_a_livrer
  FROM 
  (
      SELECT ca.reference, 
      ca.quantite AS qte_commandee
      FROM CDE_ART ca
      INNER JOIN commande c ON c.num_commande = ca.num_commande
      WHERE ca.num_commande = 1
  ) t1
  LEFT OUTER JOIN
  (
      SELECT la.reference, la.quantite_livree
      FROM LIV_ART la
      INNER JOIN LIVRAISON l ON l.code_livraison = la.code_livraison
      WHERE l.n_comm = 1
  ) t2 ON t2.reference = t1.reference
  GROUP BY t1.reference, t1.qte_commandee
  HAVING t1.qte_commandee - SUM(t2.quantite_livree) > 0
 
union
 
-- Les articles pas encore livrés
 
SELECT c.reference, c.quantite
FROM cde_art c, article a
WHERE c.reference = a.reference
AND c.num_commande = 1
AND c.reference NOT IN (SELECT a.reference
                       FROM livraison l, liv_art a
                       WHERE l.num_commande = 1
                       AND l.code_livraison = a.code_livraison)

mais il me reste l'affichage du numéro de commande, la date de la commande et le nom du fournisseur.
Croyez moi hier toute la nuit j'ai essayé de faire ça mais le problème c'est que suite à une petite modification le résultat se change, j'ai trouvé des difficultés. Si vous pouvez me donner une piste pour faire ça.

Merci pour votre compréhension et votre aide.

[CinePhil]

J'ai corrigé ma requête. Il manquait le COALESCE dans le HAVING, et bien sûr le problème de REFERNECE_FOUR.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SELECT t1.num_commande, t1.date_commande, t1.reference_four, t1.libelle_four, t1.reference, t1.qte_commandee, 
    t1.qte_commandee - COALESCE(SUM(t2.quantite_livree), 0) AS reste_a_livrer
FROM 
(
    SELECT ca.num_commande, ca.reference, c.reference_four, f.libelle_four, c.date_commande, 
    ca.quantite AS qte_commandee
    FROM CDE_ART ca
    INNER JOIN COMMANDE c ON c.num_commande = ca.num_commande
        INNER JOIN FOURNISSEUR f ON f.reference_four = c.reference_four
    WHERE ca.num_commande = 1
) t1
LEFT OUTER JOIN
(
    SELECT la.reference, la.quantite_livree
    FROM LIV_ART la
    INNER JOIN LIVRAISON l ON l.code_livraison = la.code_livraison
    WHERE l.num_commande = 1
) t2 ON t2.reference = t1.reference
GROUP BY t1.num_commande, t1.date_commande, t1.reference_four, t1.libelle_four, t1.reference, t1.qte_commandee
HAVING t1.qte_commandee - COALESCE(SUM(t2.quantite_livree), 0) > 0

Edit :
Je viens d'y ajouter les infos qui te manquaient.


Mais tes tables sont vraiment mal foutues !

[Waldar]

Edit : Edit aussi, rajout table fournisseur !

D'ailleurs, les sous-requêtes ne me paraissent pas utiles ici, je pense qu'on peut l'écrire ainsi :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
  SELECT ca.num_commande, ca.reference, c.reference_four,
         f.libelle_four, c.date_commande, ca.quantite,
         ca.quantite - COALESCE(SUM(la.quantite_livree), 0) AS reste_a_livrer
    FROM CDE_ART ca
         INNER JOIN COMMANDE c
           ON c.num_commande = ca.num_commande
         INNER JOIN FOURNISSEUR f
           ON f.reference_four = c.reference_four
         LEFT OUTER JOIN LIV_ART la
         INNER JOIN LIVRAISON l
           ON l.code_livraison = la.code_livraison
          AND l.num_commande = 1
           ON la.reference = ca.reference
   WHERE ca.num_commande = 1
GROUP BY ca.num_commande, ca.reference, c.reference_four,
         f.libelle_four, c.date_commande, ca.quantite
  HAVING ca.quantite - COALESCE(SUM(la.quantite_livree), 0) > 0;

[tunis71187]

Bonjour à tous,

Merci beaucouuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuup pour vos réponses.

Mr. Waldar,

J'ai l'erreur ORA-00904: "F"."LIBELLE_FOUR": IDENTIFICATEUR NON VALIDE.

Et merci pour votre aide.

[Waldar]

Je n'avais pas rajouté la table fournisseur de la nouvelle requête de Cinephil : j'ai corrigé le code initial.

Avez-vous aussi testé la sienne - qui doit fonctionner ?

[tunis71187]

Bonjour mes Mr,

Merci de nouveau pour vos solutions.
Je veux savoir comment à partir de ces solutions avoir les numéros de commandes et leurs fournisseurs.

à propos la structure des tables, Mr. CinePhil vous me disez que mes tables sont mal foutues, vous pouvez m'expliquer mieux que ça.

Merci.