Sélection des numéros de commandes [Résolu]

tunis71187 · 01/02/2011, 10h36

Bonjour,

J'ai essayé depuis pas mal de temps de résoudre le problème de sélection des numéros de commandes ayant comme critère les articles commandés pas encore livrés, enfin, j'ai pu s'approcher de la solution mais ma solution affiche seulement les commandes qui n'ont plus de livraison par contre il y a des commandes qui ont des livraisons incomplètes.
La requête est la suivante:

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
SELECT DISTINCT t1.num_commande, t1.date_commande,t1.libelle_four
FROM 
(
    SELECT ca.num_commande, c.date_commande, f.libelle_four, ca.quantite AS qté_commandée
    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
) t1
 FULL OUTER JOIN
(
    SELECT l.num_commande,la.quantite_livree
    FROM LIV_ART la
    INNER JOIN LIVRAISON l ON l.code_livraison = la.code_livraison
        INNER JOIN FOURNISSEUR f ON f.reference_four = l.reference_four
) t2 ON t2.num_commande = t1.num_commande
GROUP BY t1.num_commande, t1.date_commande, t1.libelle_four, t1.qte_commandee
HAVING t1.qte_commandee - COALESCE(SUM(t2.quantite_livree), 0) > 0
ORDER BY t1.num_commande

le problème après des tests réside dans l'instruction HAVING car si je fais < 0 au lieu de > 0 il y aura affichage seulement des commandes manquées de la deuxième (> 0).

Merci pour vos propositions et vos remarques.

ORA-007 · 03/02/2011, 10h41

Si j'ai bien compris :

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
 
SELECT  CASE
			WHEN t2.quantite_livree IS NULL THEN 'A faire'
			WHEN t1.quantite > t2.quantite_livree THEN 'En cours'
			WHEN t1.quantite = t2.quantite_livre THEN 'Finie'
		END AS ETAT_LIVRAISON
	   , t1.quantite - t2.quantite_livre AS Quantite_restante 	
	   ,t1.*
  FROM (SELECT ca.num_commande, c.date_commande, f.libelle_four, ca.quantite
          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)
               ) t1,
       (SELECT l.num_commande, la.quantite_livree
          FROM LIV_ART la 
		  	   INNER JOIN LIVRAISON l ON (l.code_livraison = la.code_livraison)
               ) t2
 WHERE 1 = 1 
   AND t1.num_commande = t2.num_commande(+)

tunis71187 · 09/02/2011, 09h47

Bonjour,

Merci pour votre proposition Mr. ORA-007. J'ai testé votre requête mais le problème c'est que je cherche juste les numéros de commande qui ont le critère ' A FAIRE ' ou ' EN COURS '.

Merci de nouveau.

ORA-007 · 09/02/2011, 11h37

Pour avoir les commandes en cours :

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
 
SELECT num_commande
FROM 
(
SELECT  CASE
			WHEN t2.quantite_livree IS NULL THEN 'A faire'
			WHEN t1.quantite > t2.quantite_livree THEN 'En cours'
			WHEN t1.quantite = t2.quantite_livre THEN 'Finie'
		END AS ETAT_LIVRAISON
	   , t1.quantite - t2.quantite_livre AS Quantite_restante 	
	   ,t1.*
  FROM (SELECT ca.num_commande, c.date_commande, f.libelle_four, ca.quantite
          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)
               ) t1,
       (SELECT l.num_commande, la.quantite_livree
          FROM LIV_ART la 
		  	   INNER JOIN LIVRAISON l ON (l.code_livraison = la.code_livraison)
               ) t2
 WHERE 1 = 1 
   AND t1.num_commande = t2.num_commande(+) 
)
WHERE ETAT_LIVRAISON <> 'FINIE'

tunis71187 · 11/02/2011, 11h46

Bonjour Mr ORA-OO7,

ça m'affiche tous les commandes mêmes qui sont finies.

Je propose un jeu de test pour être au même niveau:

soit 2 fournisseurs, 4 commandes (1,2,3,4) et 10 articles (art1-art10) réparties comme suis:

commande 1 du fournisseur1: art1 de quantité 10, art2 de quantité 11.
commande 2 du fournisseur2: art3 de quantité 15, art4 de quantité 16, art5 de quantité 20.
commande 3 du fournisseur2: art6 de quantité 15, art7 de quantité 20.
commande 4 du fournisseur3: art7 de quantité 5, art8 de quantité 10, art9 de quantité 20, art10 de quantité 15.

Merci.

et 6 livraisons:
Livraison 1 pour la commande 1: art1 de quantité 9, art2 de quantité 11.
Livraison 2 pour la commande 2: art3 de quantité 15, art4 de quantité 10.
Livraison 3 pour la commande 2: art5 de quantité 15.
Livraison 4 pour la commande 3: art6 de quantité 15, art7 de quantité 20.
Livraison 5 pour la commande 4: art7 de quantité 5, art8 de quantité 10.
Livraison 6 pour la commande 4: art10 de quantité 10.

donc comme résultat c'est les commandes : 1,2 et 4 qui sont en cours.

salim11 · 11/02/2011, 16h01

Salut,

Pour mieux vous aider, est ce que c'est possible de nous poster :

1- Script de creation de tes table (create table ...)
2- Script d'insertion deas données (insert into ....) un jeu d'essai.

Cordialement Salim.

tunis71187 · 11/02/2011, 16h36

Bonjour Mr. Salim11,

Ci-dessous les scripts de création des tables et d'insertion de données:

1) Création des 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
67
CREATE TABLE ARTICLE
(
  REFERENCE   VARCHAR2(50) NOT NULL,
  DESIGNATION VARCHAR2(150)
)
ALTER TABLE ARTICLE
  ADD constraint PK_ARTICLE PRIMARY KEY (REFERENCE)
CREATE TABLE FOURNISSEUR
(
  REFERENCE_FOUR VARCHAR2(50) NOT NULL,
  LIBELLE_FOUR   VARCHAR2(100)
)
ALTER TABLE FOURNISSEUR
  ADD constraint PK_FOURNISSEUR PRIMARY KEY (REFERENCE_FOUR)
CREATE TABLE COMMANDE
(
  NUM_COMMANDE   VARCHAR2(50) NOT NULL,
  REFERENCE_FOUR VARCHAR2(50),
  DATE_COMMANDE  DATE
)
ALTER TABLE COMMANDE
  ADD constraint PK_COMMANDE PRIMARY KEY (NUM_COMMANDE)
ALTER TABLE COMMANDE
  ADD constraint FK1_COMMANDE FOREIGN KEY (REFERENCE_FOUR)
  REFERENCES FOURNISSEUR (REFERENCE_FOUR);
CREATE TABLE CDE_ART
(
  NUM_COMMANDE VARCHAR2(50) NOT NULL,
  REFERENCE    VARCHAR2(50) NOT NULL,
  QUANTITE     NUMBER
)
ALTER TABLE CDE_ART
  ADD constraint PK_CDE_ART PRIMARY KEY (REFERENCE, NUM_COMMANDE)
ALTER TABLE CDE_ART
  ADD constraint FK1_CDE_ART FOREIGN KEY (NUM_COMMANDE)
  REFERENCES COMMANDE (NUM_COMMANDE);
ALTER TABLE CDE_ART
  ADD constraint FK2_CDE_ART FOREIGN KEY (REFERENCE)
  REFERENCES ARTICLE (REFERENCE);
CREATE TABLE LIVRAISON
(
  CODE_LIVRAISON VARCHAR2(50) NOT NULL,
  REFERENCE_FOUR VARCHAR2(50),
  NUM_COMMANDE   VARCHAR2(50),
  DATE_LIVRAISON DATE
)
ALTER TABLE LIVRAISON
  ADD constraint PK_LIVRAISON PRIMARY KEY (CODE_LIVRAISON)
ALTER TABLE LIVRAISON
  ADD constraint FK1_LIVRAISON FOREIGN KEY (REFERENCE_FOUR)
  REFERENCES FOURNISSEUR (REFERENCE_FOUR);
ALTER TABLE LIVRAISON
  ADD constraint FK2_LIVRAISON FOREIGN KEY (NUM_COMMANDE)
CREATE TABLE LIV_ART
(
  CODE_LIVRAISON  VARCHAR2(50) NOT NULL,
  REFERENCE       VARCHAR2(50) NOT NULL,
  QUANTITE_LIVREE NUMBER
)
ALTER TABLE LIV_ART
  ADD constraint PK_LIV_ART PRIMARY KEY (CODE_LIVRAISON, REFERENCE)
ALTER TABLE LIV_ART
  ADD constraint FK1_LIV_ART FOREIGN KEY (CODE_LIVRAISON)
  REFERENCES LIVRAISON (CODE_LIVRAISON);
ALTER TABLE LIV_ART
  ADD constraint FK2_LIV_ART FOREIGN KEY (REFERENCE)
  REFERENCES ARTICLE (REFERENCE);

2) Script d'insertion:

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
 
INSERT INTO article (REFERENCE, DESIGNATION)
VALUES ('1', 'art1');
 
INSERT INTO article (REFERENCE, DESIGNATION)
VALUES ('2', 'art2');
 
INSERT INTO article (REFERENCE, DESIGNATION)
VALUES ('3', 'art3');
 
INSERT INTO article (REFERENCE, DESIGNATION)
VALUES ('4', 'art4');
 
INSERT INTO article (REFERENCE, DESIGNATION)
VALUES ('5', 'art5');
 
INSERT INTO article (REFERENCE, DESIGNATION)
VALUES ('6', 'art6');
 
INSERT INTO article (REFERENCE, DESIGNATION)
VALUES ('7', 'art7');
 
INSERT INTO article (REFERENCE, DESIGNATION)
VALUES ('8', 'art8');
 
INSERT INTO article (REFERENCE, DESIGNATION)
VALUES ('9', 'art9');
 
INSERT INTO article (REFERENCE, DESIGNATION)
VALUES ('10', 'art10');
INSERT INTO fournisseur (REFERENCE_FOUR, LIBELLE_FOUR)
VALUES ('1', 'ref1');
 
INSERT INTO fournisseur (REFERENCE_FOUR, LIBELLE_FOUR)
VALUES ('2', 'ref2');
 
INSERT INTO fournisseur (REFERENCE_FOUR, LIBELLE_FOUR)
VALUES ('3', 'ref3');
INSERT INTO commande (NUM_COMMANDE, REFERENCE_FOUR, DATE_COMMANDE)
VALUES ('1', '1', to_date('11-11-2010', 'dd-mm-yyyy'));
 
INSERT INTO commande (NUM_COMMANDE, REFERENCE_FOUR, DATE_COMMANDE)
VALUES ('2', '2', to_date('10-01-2011', 'dd-mm-yyyy'));
 
INSERT INTO commande (NUM_COMMANDE, REFERENCE_FOUR, DATE_COMMANDE)
VALUES ('3', '2', to_date('20-01-2011', 'dd-mm-yyyy'));
 
INSERT INTO commande (NUM_COMMANDE, REFERENCE_FOUR, DATE_COMMANDE)
VALUES ('4', '3', to_date('30-01-2011', 'dd-mm-yyyy'));
INSERT INTO cde_art (NUM_COMMANDE, REFERENCE, QUANTITE)
VALUES ('1', '1', 10);
 
INSERT INTO cde_art (NUM_COMMANDE, REFERENCE, QUANTITE)
VALUES ('1', '2', 11);
 
INSERT INTO cde_art (NUM_COMMANDE, REFERENCE, QUANTITE)
VALUES ('2', '3', 15);
 
INSERT INTO cde_art (NUM_COMMANDE, REFERENCE, QUANTITE)
VALUES ('2', '4', 16);
 
INSERT INTO cde_art (NUM_COMMANDE, REFERENCE, QUANTITE)
VALUES ('2', '5', 20);
 
INSERT INTO cde_art (NUM_COMMANDE, REFERENCE, QUANTITE)
VALUES ('3', '6', 15);
 
INSERT INTO cde_art (NUM_COMMANDE, REFERENCE, QUANTITE)
VALUES ('3', '7', 20);
 
INSERT INTO cde_art (NUM_COMMANDE, REFERENCE, QUANTITE)
VALUES ('4', '8', 10);
 
INSERT INTO cde_art (NUM_COMMANDE, REFERENCE, QUANTITE)
VALUES ('4', '9', 20);
 
INSERT INTO cde_art (NUM_COMMANDE, REFERENCE, QUANTITE)
VALUES ('4', '10', 15);
 
INSERT INTO cde_art (NUM_COMMANDE, REFERENCE, QUANTITE)
VALUES ('4', '7', 5);
INSERT INTO livraison (CODE_LIVRAISON, REFERENCE_FOUR, NUM_COMMANDE, DATE_LIVRAISON)
VALUES ('1', '1', '1', to_date('20-12-2010', 'dd-mm-yyyy'));
 
INSERT INTO livraison (CODE_LIVRAISON, REFERENCE_FOUR, NUM_COMMANDE, DATE_LIVRAISON)
VALUES ('2', '2', '2', to_date('15-01-2011', 'dd-mm-yyyy'));
 
INSERT INTO livraison (CODE_LIVRAISON, REFERENCE_FOUR, NUM_COMMANDE, DATE_LIVRAISON)
VALUES ('3', '2', '2', to_date('22-01-2011', 'dd-mm-yyyy'));
 
INSERT INTO livraison (CODE_LIVRAISON, REFERENCE_FOUR, NUM_COMMANDE, DATE_LIVRAISON)
VALUES ('4', '2', '3', to_date('25-01-2011', 'dd-mm-yyyy'));
 
INSERT INTO livraison (CODE_LIVRAISON, REFERENCE_FOUR, NUM_COMMANDE, DATE_LIVRAISON)
VALUES ('5', '3', '4', to_date('02-02-2011', 'dd-mm-yyyy'));
 
INSERT INTO livraison (CODE_LIVRAISON, REFERENCE_FOUR, NUM_COMMANDE, DATE_LIVRAISON)
VALUES ('6', '3', '4', to_date('05-02-2011', 'dd-mm-yyyy'));
INSERT INTO liv_art (CODE_LIVRAISON, REFERENCE, QUANTITE_LIVREE, ROWID)
VALUES ('1', '1', 9, 'AAAM2PAAEAAAAOtAAA');
 
INSERT INTO liv_art (CODE_LIVRAISON, REFERENCE, QUANTITE_LIVREE, ROWID)
VALUES ('1', '2', 11, 'AAAM2PAAEAAAAOtAAB');
 
INSERT INTO liv_art (CODE_LIVRAISON, REFERENCE, QUANTITE_LIVREE, ROWID)
VALUES ('2', '3', 15, 'AAAM2PAAEAAAAOtAAC');
 
INSERT INTO liv_art (CODE_LIVRAISON, REFERENCE, QUANTITE_LIVREE, ROWID)
VALUES ('2', '4', 10, 'AAAM2PAAEAAAAOtAAD');
 
INSERT INTO liv_art (CODE_LIVRAISON, REFERENCE, QUANTITE_LIVREE, ROWID)
VALUES ('3', '5', 15, 'AAAM2PAAEAAAAOtAAE');
 
INSERT INTO liv_art (CODE_LIVRAISON, REFERENCE, QUANTITE_LIVREE, ROWID)
VALUES ('4', '6', 15, 'AAAM2PAAEAAAAOtAAF');
 
INSERT INTO liv_art (CODE_LIVRAISON, REFERENCE, QUANTITE_LIVREE, ROWID)
VALUES ('4', '7', 20, 'AAAM2PAAEAAAAOtAAG');
 
INSERT INTO liv_art (CODE_LIVRAISON, REFERENCE, QUANTITE_LIVREE, ROWID)
VALUES ('5', '8', 10, 'AAAM2PAAEAAAAOtAAH');
 
INSERT INTO liv_art (CODE_LIVRAISON, REFERENCE, QUANTITE_LIVREE, ROWID)
VALUES ('5', '9', 10, 'AAAM2PAAEAAAAOtAAI');
 
INSERT INTO liv_art (CODE_LIVRAISON, REFERENCE, QUANTITE_LIVREE, ROWID)
VALUES ('5', '7', 5, 'AAAM2PAAEAAAAOtAAJ');
 
INSERT INTO liv_art (CODE_LIVRAISON, REFERENCE, QUANTITE_LIVREE, ROWID)
VALUES ('6', '10', 10, 'AAAM2PAAEAAAAOtAAK');

Merci pour vos propositions.

McM · 12/02/2011, 14h04

Voici la requête qui permet d'afficher toutes les cdes fournisseurs qui n'ont pas eu leurs articles complètement livrés. Cette requête ne gère pas la sur livraison (qte livrée > qte commande)

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
WITH cde_art AS (SELECT '1' NUM_COMMANDE, '1' REFERENCE, 10 QUANTITE FROM dual
union ALL SELECT '1', '2', 11 FROM dual
union ALL SELECT '2', '3', 15 FROM dual
union ALL SELECT '2', '4', 16 FROM dual
union ALL SELECT '2', '5', 20 FROM dual
union ALL SELECT '3', '6', 15 FROM dual
union ALL SELECT '3', '7', 20 FROM dual
union ALL SELECT '4', '8', 10 FROM dual
union ALL SELECT '4', '9', 20 FROM dual
union ALL SELECT '4', '10', 15 FROM dual
union ALL SELECT '4', '7', 5 FROM dual
),
livraison AS (SELECT '1' CODE_LIVRAISON, '1' REFERENCE_FOUR, '1' NUM_COMMANDE, to_date('20-12-2010', 'dd-mm-yyyy') DATE_LIVRAISON FROM dual
union ALL SELECT '2', '2', '2', to_date('15-01-2011', 'dd-mm-yyyy') FROM dual
union ALL SELECT '3', '2', '2', to_date('22-01-2011', 'dd-mm-yyyy') FROM dual
union ALL SELECT '4', '2', '3', to_date('25-01-2011', 'dd-mm-yyyy') FROM dual
union ALL SELECT '5', '3', '4', to_date('02-02-2011', 'dd-mm-yyyy') FROM dual
union ALL SELECT '6', '3', '4', to_date('05-02-2011', 'dd-mm-yyyy') FROM dual
),
liv_art AS (SELECT '1' CODE_LIVRAISON, '1' REFERENCE, 9 QUANTITE_LIVREE FROM dual
union ALL SELECT '1', '2', 11  FROM dual
union ALL SELECT '2', '3', 15  FROM dual
union ALL SELECT '2', '4', 10  FROM dual
union ALL SELECT '3', '5', 15  FROM dual
union ALL SELECT '4', '6', 15  FROM dual
union ALL SELECT '4', '7', 20  FROM dual
union ALL SELECT '5', '8', 10  FROM dual
union ALL SELECT '5', '9', 10  FROM dual
union ALL SELECT '5', '7',  5  FROM dual
union ALL SELECT '6', '10', 10 FROM dual)
SELECT DISTINCT num_commande
FROM (SELECT num_commande, reference, sum(quantite) 
	FROM cde_art
	GROUP BY num_commande, reference
	minus
	SELECT l.num_commande, a.reference, sum(a.quantite_livree) 
	FROM liv_art a, livraison l
	WHERE a.code_livraison = l.code_livraison
	GROUP BY l.num_commande, a.reference
	)

Sinon en testant les différences

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
SELECT DISTINCT num_commande FROM 
(SELECT num_commande, reference 
FROM cde_art c
GROUP BY num_commande, reference
HAVING sum(quantite) <> NVL( 
			(SELECT sum(a.quantite_livree) 
				FROM liv_art a, livraison l
				WHERE l.num_commande = c.num_commande
				AND a.reference = c.reference
				AND a.code_livraison = l.code_livraison), 0)
)
 
"NUM_COMMANDE"                
"1"                           
"2"                           
"4"

11/02/2011, 16h01	#6
salim11 Rédacteur Salim Développeur et DBA Oracle Inscription : octobre 2006 Messages : 872 Détails du profil Informations personnelles : Nom : Salim Localisation : Canada Informations professionnelles : Activité : Développeur et DBA Oracle Informations forums : Inscription : octobre 2006 Messages : 872 Points : 1 100 Points : 1 100	Salut, Pour mieux vous aider, est ce que c'est possible de nous poster : 1- Script de creation de tes table (create table ...) 2- Script d'insertion deas données (insert into ....) un jeu d'essai. Cordialement Salim. __________________ Publications: http://schelabi.developpez.com/
	00

09/02/2011, 09h47	#3
tunis71187 Membre du Club Inscription : novembre 2007 Messages : 226 Détails du profil Informations forums : Inscription : novembre 2007 Messages : 226 Points : 66 Points : 66	Bonjour, Merci pour votre proposition Mr. ORA-007. J'ai testé votre requête mais le problème c'est que je cherche juste les numéros de commande qui ont le critère ' A FAIRE ' ou ' EN COURS '. Merci de nouveau.
	00

11/02/2011, 11h46	#5
tunis71187 Membre du Club Inscription : novembre 2007 Messages : 226 Détails du profil Informations forums : Inscription : novembre 2007 Messages : 226 Points : 66 Points : 66	Bonjour Mr ORA-OO7, ça m'affiche tous les commandes mêmes qui sont finies. Je propose un jeu de test pour être au même niveau: soit 2 fournisseurs, 4 commandes (1,2,3,4) et 10 articles (art1-art10) réparties comme suis: commande 1 du fournisseur1: art1 de quantité 10, art2 de quantité 11. commande 2 du fournisseur2: art3 de quantité 15, art4 de quantité 16, art5 de quantité 20. commande 3 du fournisseur2: art6 de quantité 15, art7 de quantité 20. commande 4 du fournisseur3: art7 de quantité 5, art8 de quantité 10, art9 de quantité 20, art10 de quantité 15. Merci. et 6 livraisons: Livraison 1 pour la commande 1: art1 de quantité 9, art2 de quantité 11. Livraison 2 pour la commande 2: art3 de quantité 15, art4 de quantité 10. Livraison 3 pour la commande 2: art5 de quantité 15. Livraison 4 pour la commande 3: art6 de quantité 15, art7 de quantité 20. Livraison 5 pour la commande 4: art7 de quantité 5, art8 de quantité 10. Livraison 6 pour la commande 4: art10 de quantité 10. donc comme résultat c'est les commandes : 1,2 et 4 qui sont en cours.
	00

Outils de la discussion
Afficher une version imprimable Envoyer un lien vers cette page par email