Sélection des numéros de commandes

Version imprimable

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:

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.

Si j'ai bien compris :

Code:

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(+)

09/02/2011, 09h47
tunis71187

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.

Pour avoir les commandes en cours :

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
 
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'

11/02/2011, 11h46
tunis71187

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.
11/02/2011, 16h01
salim11

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.

Bonjour Mr. Salim11,

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

1) Création des 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
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:

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.

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:

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:

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"