requete commandes/receptions

Question

Bonjour,

J'ai un problème (comme vous vous en doutez) avec une requête :

J'ai 3 tables :

CREATE TABLE  "COMPTA" 
   (    "CC" NUMBER NOT NULL ENABLE, 
     CONSTRAINT "COMPTA_PK" PRIMARY KEY ("CC") ENABLE
   )
/

CREATE OR REPLACE TRIGGER  "BI_COMPTA" 
  before insert on "COMPTA"               
  for each row  
begin   
    select "COMPTA_SEQ".nextval into :NEW.CC from dual; 
end; 

/
ALTER TRIGGER  "BI_COMPTA" ENABLE
/

INSERT INTO "COMPTA" VALUES (101);
INSERT INTO "COMPTA" VALUES (102);
INSERT INTO "COMPTA" VALUES (103);
INSERT INTO "COMPTA" VALUES (201);
INSERT INTO "COMPTA" VALUES (202);
INSERT INTO "COMPTA" VALUES (203);

CREATE TABLE  "CMDES" 
   (    "ID" NUMBER NOT NULL ENABLE, 
    "CME" VARCHAR2(4000) NOT NULL ENABLE, 
    "MNT" NUMBER NOT NULL ENABLE, 
    "CC" NUMBER NOT NULL ENABLE, 
     CONSTRAINT "CMDES_PK" PRIMARY KEY ("ID") ENABLE, 
     CONSTRAINT "CMDES_FK" FOREIGN KEY ("CC")
      REFERENCES  "COMPTA" ("CC") ENABLE
   )
/

CREATE OR REPLACE TRIGGER  "BI_CMDES" 
  before insert on "CMDES"               
  for each row  
begin   
    select "CMDES_SEQ".nextval into :NEW.ID from dual; 
end; 

/
ALTER TRIGGER  "BI_CMDES" ENABLE
/

INSERT INTO "CMDES" VALUES (1,'CME1',30,102);
INSERT INTO "CMDES" VALUES (2,'CME1',40,102);
INSERT INTO "CMDES" VALUES (3,'CME1',200,103);
INSERT INTO "CMDES" VALUES (4,'CME1',300,103);
INSERT INTO "CMDES" VALUES (5,'CME1',400,103);
INSERT INTO "CMDES" VALUES (6,'CME2',10,201);
INSERT INTO "CMDES" VALUES (7,'CME2',20,101);

CREATE TABLE  "RECEP" 
   (    "ID" NUMBER NOT NULL ENABLE, 
    "CME" VARCHAR2(4000) NOT NULL ENABLE, 
    "MNT" NUMBER NOT NULL ENABLE, 
    "CC" NUMBER NOT NULL ENABLE, 
     CONSTRAINT "RECEP_PK" PRIMARY KEY ("ID") ENABLE, 
     CONSTRAINT "RECEP_FK" FOREIGN KEY ("CC")
      REFERENCES  "COMPTA" ("CC") ENABLE
   )
/

CREATE OR REPLACE TRIGGER  "BI_RECEP" 
  before insert on "RECEP"               
  for each row  
begin   
    select "RECEP_SEQ".nextval into :NEW.ID from dual; 
end; 

/
ALTER TRIGGER  "BI_RECEP" ENABLE
/

INSERT INTO "RECEP" VALUES (1,'CME1',10,101);
INSERT INTO "RECEP" VALUES (2,'CME1',20,101);
INSERT INTO "RECEP" VALUES (3,'CME1',50,102);
INSERT INTO "RECEP" VALUES (4,'CME1',700,202);
INSERT INTO "RECEP" VALUES (5,'CME2',40,201);
INSERT INTO "RECEP" VALUES (6,'CME2',50,202);

Ces 2 requêtes marchent bien :

SELECT COMPTA.cc, SUM(CMDES.mnt) as "cmdes"
FROM COMPTA, CMDES
WHERE CMDES.cme = 'CME1'
AND CMDES.cc = COMPTA.cc
GROUP BY COMPTA.cc
ORDER BY COMPTA.cc;

SELECT COMPTA.cc, SUM(RECEP.mnt) as "recep"
FROM COMPTA, RECEP
WHERE RECEP.cme = 'CME1'
AND RECEP.cc = COMPTA.cc
GROUP BY COMPTA.cc
ORDER BY COMPTA.cc;

Ce que je voudrais faire, c'est une "fusion" de ces deux résultats, quelque chose qui donnerait un truc du genre (pour cme = 'CME1') :
cc        cmdes    recep
101        -        30
102        70        50
103        900        -
202        -        700

J'ai essayé la requête :
SELECT COMPTA.cc, SUM(CMDES.mnt) as "cmdes", SUM(RECEP.mnt) as "recep"
FROM COMPTA, CMDES, RECEP
WHERE (CMDES.cme = 'CME1'
AND CMDES.cc = COMPTA.cc)
OR (RECEP.cme = 'CME1'
AND RECEP.cc = COMPTA.cc)
GROUP BY COMPTA.cc
ORDER BY COMPTA.cc;

Mais ce n'ai pas ce que veux.

Quelqu'un peut-il m'aider ?

Merci d'avance.

pneau · Answer

salut,

essaies cela

SELECT COMPTA.cc, SUM(CMDES.mnt) as "cmdes", SUM(RECEP.mnt) as "recep"
FROM COMPTA, CMDES, RECEP
WHERE CMDES.cme 'CME1' AND CMDES.cc COMPTA.cc and RECEP.cme = 'CME1'AND RECEP.cc COMPTA.cc And CMDES.cc RECEP.cc
GROUP BY COMPTA.cc
ORDER BY COMPTA.cc;

dis moi le result et on verra ensuite..
@+

Pat

 Don't Worry , Be Happy 

<hr />lorsque le problème est résolu, pensez Réponse Acceptée

dmk04 · Answer

cc    cmdes    recep
102    70    100

Je me rapproche du resultat voulu avec :
SELECT COMPTA.cc, SUM(CMDES.mnt) as "cmdes", SUM(RECEP.mnt) as "recep"
FROM COMPTAFULL OUTER JOIN CMDES ON CMDES.cc COMPTA.cc AND CMDES.cme 'CME1'FULL OUTER JOIN RECEP ON RECEP.cc COMPTA.cc AND RECEP.cme 'CME1'
WHERE COMPTA.cc IS NOT NULL
GROUP BY COMPTA.cc
ORDER BY COMPTA.cc;

mais j'ai :
cc        cmdes    recep
101        -        30
102        70       100
103        900        -
202        -        700

en regardant le détail, pour cc=102 j'ai :
102        30        50
102        40        50

J'ai une ligne avec cc=102 dans CMDES et 2 lignes avec cc=102 dans RECEP donc il double. Peut-on annuler cet effet ?

Voilà où j'en suis pour le moment.

pneau · Answer

salut (re)
as tu essayer avec l'opérateur "UNION" en utilisant tes 2 requetes de base
ex :
SELECT COMPTA.cc, SUM(CMDES.mnt) as "cmdes", 0 as "recep"
FROM COMPTA, CMDES
WHERE CMDES.cme =  'CME1'
AND CMDES.cc = COMPTA.cc
GROUP BY COMPTA.cc
ORDER BY COMPTA.cc;
 UNION 
SELECT COMPTA.cc, 0 as "cmdes", SUM(RECEP.mnt) as "recep"
FROM COMPTA, RECEP
WHERE RECEP.cme  = 'CME1'
AND RECEP.cc = COMPTA.cc
GROUP BY COMPTA.cc
ORDER BY COMPTA.cc;

Attention, je n'ai pas fait de PL/SQL depuis un moment donc si il y a une err de syntaxe, vérifie les paramètres du UNION

cordialement

Pat

 Don't Worry , Be Happy 

<hr />lorsque le problème est résolu, pensez Réponse Acceptée

dmk04 · Answer

UNION met bout à bout les 2 résultats. Avec la syntax correcte, j'aurai mes commandes et receptions dans la même colonne...

La solution que j'ai trouvé pour le moment, c'est de créer 2 vues :
CREATE VIEW cmdesCME1 AS
SELECT COMPTA.cc as "cc", SUM(CMDES.mnt) as "cmdes"
FROM COMPTA, CMDES
WHERE CMDES.cme = 'CME1'
AND CMDES.cc = COMPTA.cc
GROUP BY COMPTA.cc
ORDER BY COMPTA.cc;

CREATE VIEW recepCME1 AS
SELECT COMPTA.cc as "cc", SUM(RECEP.mnt) as "recep"
FROM COMPTA, RECEP
WHERE RECEP.cme = 'CME1'
AND RECEP.cc = COMPTA.cc
GROUP BY COMPTA.cc
ORDER BY COMPTA.cc;

Et apres de faire une jointure des deux vues :
SELECT COMPTA.cc as "cc", cmdesCME1."cmdes" as "cmdes", recepCME1."recep" as "recep"
FROM COMPTA
FULL OUTER JOIN cmdesCME1 ON cmdesCME1."cc" = COMPTA.cc
FULL OUTER JOIN recepCME1 ON recepCME1."cc" = COMPTA.cc
WHERE COMPTA.cc IS NOT NULL
AND (cmdesCME1."cmdes" IS NOT NULL
OR recepCME1."recep" IS NOT NULL)
ORDER BY COMPTA.cc;

Requete commandes/receptions

4 réponses

Votre réponse

Discussions similaires