Problème sur une requête SQL (PL/SQL)

Bonjour,

Je suis entrain de faire un projet de pl/sql. J'ai un petit souci d'optimisation d'une procédure.

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
 
CREATE TABLE tclient (
  idclient int PRIMARY KEY,
  nom varchar(100),
  prenom varchar(100),
  adresse  varchar(600),
  cp varchar(10),
  vil varchar(100),
  tel varchar(10)
);
 
-- --------------------------------------------------------
--
-- Structure de la table tvehicule
--
 
CREATE TABLE tvehicule (
  immat varchar(25) PRIMARY KEY,
  type varchar(25) ,
  marque varchar(100),
  modele varchar(100),
  idclient int,
  FOREIGN KEY (idclient) REFERENCES tclient(idclient),
  CONSTRAINT invalid_type_vehicule CHECK( type in('tvoiture','tmoto'))
) ;
 
-- --------------------------------------------------------
--
-- Structure de la table tvoiture
--
 
CREATE TABLE tvoiture (
  immat varchar(25) PRIMARY KEY, 
  carburant varchar(100) ,
  nbporte int, 
  FOREIGN KEY (immat) REFERENCES tvehicule(immat)
) ;
-- --------------------------------------------------------
--
-- Structure de la table tmoto
--
 
CREATE TABLE tmoto (
  immat varchar(25) PRIMARY KEY,
  cylindre int ,
  categorie varchar(100),
  FOREIGN KEY (immat) REFERENCES tvehicule(immat)
) ;
 
 
 
---------------------------------------------
--
-- Structure de la table tfacture
--
CREATE TABLE tfacture (
  idfacture int PRIMARY KEY,
  fdate date,
  paiement real,
  km int,
  immat varchar(25),
  FOREIGN KEY (immat) REFERENCES tvehicule(immat)
) ;
--------------------------------------------------------------
--
-- Structure de la table tfligne
--
 
CREATE TABLE tfligne (
  idligne int PRIMARY KEY,
  txt varchar(255),
  qte real ,
  prixunit real,
  tva real ,
  idfacture int,
  FOREIGN KEY (idfacture) REFERENCES  tfacture(idfacture)
 
) ;
 
INSERT INTO tclient VALUES (0,'MOSBY','Ted','222 Baker Street','X54RT','NEW YORK','0978877761');
INSERT INTO tclient VALUES (1,'ALDRIN','Lily','222 Baker Street','X54RT','NEW YORK','0978877761');
INSERT INTO tclient VALUES (2,'ERIKSEN','Marshall','222 Baker Street','X54RT','NEW YORK','0978877761');
INSERT INTO tclient VALUES (3,'STINSON','Barney','2165 Looghe Road','X54RT','NEW YORK','0988767321');
INSERT INTO tclient VALUES (4,'SCHERBATSKY','Robin','12 Canada Street','X54RT','NEW YORK','0977553433');
 
 
INSERT INTO tvehicule VALUES('2344TF54','tvoiture','RENAULT','R5',1);
INSERT INTO tvehicule VALUES('430TD79','tvoiture','BMW','X3',3);
INSERT INTO tvehicule VALUES('3024SZ79','tmoto','BMW','K1200R',3);
INSERT INTO tvehicule VALUES('1730SG79','tvoiture','BMW','X2',2);
INSERT INTO tvehicule VALUES('8133ST79','tmoto','SUZIKI','GSXR',4);
INSERT INTO tvehicule VALUES('7290TL75','tvoiture','PEUGEOT','1004',0);
 
 
INSERT INTO tfacture VALUES(0,sysdate,50,56679,'1730SG79');
INSERT INTO tfligne VALUES(0,'Main d oeuvre',1,30,19.6,0);
INSERT INTO tfligne VALUES(1,'Plaquette de frein',2,12,19.6,0);
INSERT INTO tfligne VALUES(2,'Disque',1,25,19.6,0);
INSERT INTO tfligne VALUES(3,'Divers',2,12,19.6,0);
 
INSERT INTO tfacture VALUES(1,sysdate,99.268,78679,'8133ST79');
INSERT INTO tfligne VALUES(4,'Main d oeuvre',1,30,19.6,1);
INSERT INTO tfligne VALUES(5,'Huile',2,12,19.6,1);
INSERT INTO tfligne VALUES(6,'Filtre',1,5,19.6,1);
INSERT INTO tfligne VALUES(7,'Divers',2,12,19.6,1);
 
INSERT INTO tfacture VALUES(2,sysdate,0,123679,'7290TL75');
INSERT INTO tfligne VALUES(8,'Main d oeuvre',1,30,19.6,2);
INSERT INTO tfligne VALUES(9,'Pneu',4,124,19.6,2);
 
INSERT INTO tfacture VALUES(3,sysdate,50,70679,'1730SG79');
INSERT INTO tfligne VALUES(10,'Main d oeuvre',1,30,19.6,0);
INSERT INTO tfligne VALUES(11,'Plaquette de frein',2,12,19.6,0);
INSERT INTO tfligne VALUES(12,'Divers',2,12,19.6,0);
 
INSERT INTO tfacture VALUES(4,sysdate,50,56679,'1730SG79');
INSERT INTO tfligne VALUES(13,'Main d oeuvre',1,30,19.6,0);
INSERT INTO tfligne VALUES(14,'Plaquette de frein',2,12,19.6,0);
INSERT INTO tfligne VALUES(15,'Disque',1,25,19.6,0);
INSERT INTO tfligne VALUES(16,'Divers',2,12,19.6,0);

je travaille selon ce modéle:
Tclient(Idclient,nom, prenom,adresse..)
Tvehicule(Immat,marque, modèle,...,#idclient)
Tfacture(idfacture,date,paiement,#immat)
Tfligne(idligne,txt,prixunit,qte,tva,#idfacture)

Tfacture correspond à l'entete de la facture
Tfligne correspond à une ligne de la facture
paiement correspond au prix payé par le client ( dans l'idée qui puisse payer en plusieurs fois)

Le but de ma procédure doit me retourner les mauvais payeurs avec le montant de leurs dettes.
Je souhaiterais le faire en une seule requête 8-)

Code:

1
2
3
4
5
SELECT cli.idclient,SUM(prixunit*qte+(prixunit*qte*tva)/100)- SUM(fac.paiement) AS MONTANT
FROM tclient cli, tvehicule veh, tfacture fac, tfligne lig
WHERE cli.idclient=veh.idclient AND veh.immat=fac.immat AND fac.idfacture=lig.idfacture
GROUP BY cli.idclient
ORDER BY MONTANT DESC;

Le souci vient des jointures pour une seule facture,SUM(fac.paiement) me compte plusieurs fois le montant paiement, à vérifier mais serment le même nombre de fois qu'il y a de ligne.

Je visualise à peu prêt le problème mais ne trouve pas de solution sans faire 36 milles requête.:aie:

Merci

Goofy

Citation:

Envoyé par goofyrocks

Code:

...SUM(fac.paiement) AS MONTANT

pourquoi tu utilise 'sum(fac.paiment)', c'est faux.

La requete est la suivante:
Code:

1 2 3 4 5 SELECT cli.idclient,(SUM(prixunit*qte+(prixunit*qte*tva)/100)- fac.paiement) AS MONTANT FROM tclient cli, tvehicule veh, tfacture fac, tfligne lig WHERE cli.idclient=veh.idclient AND veh.immat=fac.immat AND fac.idfacture=lig.idfacture GROUP BY cli.idclient ORDER BY MONTANT DESC;
:yaisse2:

Merci de respecter la charte de postage de ce forum en publiant le DDL de vos table...
http://www.developpez.net/forums/a69...gage-sql-lire/

A +

OU comme ça:

Code:

1
2
3
4
5
SELECT cli.idclient, (cal.payer- fac.paiement) AS MONTANT
from client, (select cli.idclient , SUM(prixunit*qte+(prixunit*qte*tva)/100)
                FROM tclient cli, tvehicule veh, tfacture fac, tfligne lig
              WHERE cli.idclient=veh.idclient AND veh.immat=fac.immat AND          fac.idfacture=lig.idfacture
              GROUP BY cli.idclient) as cal;

:yaisse2:

Citation:

Envoyé par hazamor

pourquoi tu utilise 'sum(fac.paiment)', c'est faux.

Pourquoi un client ne pourrait-il pas avoir plusieurs factures (s'il a plusieurs voitures par exemple) ?

Il faut d'abord regrouper l'état des paiments par facture, puis par voiture, et enfin par client.

Citation:

Envoyé par Waldar

Pourquoi un client ne pourrait-il pas avoir plusieurs factures (s'il a plusieurs voitures par exemple) ?

Il faut d'abord regrouper l'état des paiments par facture, puis par voiture, et enfin par client.

Exacte c'est pour cette raison.
J'ai rajouté le DDL dans le premier poste. Désolé de ne pas l'avoir mis plutôt.