requete commandes/receptions

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.

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

---

lorsque le problème est résolu, pensez Réponse Acceptée

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 COMPTA
FULL 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.

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

---

lorsque le problème est résolu, pensez Réponse Acceptée

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;