[SQL] Problem mit doppelter Ergebnismenge

[Igotcha]

Hallo nochmal,

das Problem bei folgendem SQL-Statement ist, dass ich die doppelte Ergebnismenge zurückbekomme - sprich die Summen sind alle doppelt so groß:

Code:

SELECT i.position, pos.posbez,
SUM(i.jan) AS JAN, SUM(i.FEB) AS FEB, SUM(i.MAR) AS MAR, SUM(i.APR) AS APR, SUM(i.MAI) AS MAI, SUM(i.JUN) AS JUN, 
SUM(i.JUL) AS JUL, SUM(p.AUG) AS AUG, SUM(p.SEP) AS SEP, SUM(p.OKT) AS OKT, SUM(p.NOV) AS NOV, SUM(p.DEZ) AS DEZ 
FROM daten_ist i, daten_prognose p, positionen pos
WHERE i.position=p.position AND i.position=pos.posid
AND i.pagid IN ("PIM5231","PIM5244")
AND pos.pgid=2000
GROUP BY i.position

Tabelle "positionen" (Ausschnitt):

Code:

"Id";"posid";"posbez";"pgid"
1;1000;"UMSATZERLÖSE";NULL
2;1060;"*Umsatz Projekte incl.F&E";2000
3;1120;"*Umsatz Wartung";2000
4;1170;"*Umsatz Beratung";2000
5;1200;"Seminare";2000

Tabelle "daten_ist" (Ausschnitt):

Code:

"Id";"PAGID";"POSITION";"JAN";"FEB";"MAR";"APR";"MAI";"JUN";"JUL";"AUG";"SEP";"OKT";"NOV";"DEZ";"variante"
1;"PIM5231";1000;0;0;0;0;0;0;0;0;0;0;0;0;NULL
2;"PIM5231";1060;0;0;0;0;282911;213891;135875;-133577;0;0;0;0;NULL
3;"PIM5231";1120;0;0;0;0;0;0;0;0;0;0;0;0;NULL
4;"PIM5231";1170;0;0;0;0;44620;44582;23840;0;0;0;0;0;NULL
5;"PIM5231";1200;0;0;0;0;0;0;0;0;0;0;0;0;NULL
140;"PIM5244";1000;0;0;0;0;0;0;0;0;0;0;0;0;NULL
141;"PIM5244";1060;0;0;0;1000000;282911;213891;135875;-133577;0;0;0;0;NULL
142;"PIM5244";1120;0;0;0;0;0;0;0;0;0;0;0;0;NULL
143;"PIM5244";1170;0;0;0;0;44620;44582;23840;0;0;0;0;0;NULL
144;"PIM5244";1200;0;0;0;0;0;0;0;0;0;0;0;0;NULL

Tabelle "daten_prognose" (Ausschnitt):

Code:

"Id";"PAGID";"POSITION";"JAN";"FEB";"MAR";"APR";"MAI";"JUN";"JUL";"AUG";"SEP";"OKT";"NOV";"DEZ";"JAN_1";"FEB_1";"MAR_1";"APR_1";"MAI_1";"JUN_1"
1;"PIM5231";1000;0;0;0;0;0;0;0;0;0;0;0;0;1000;2000;3000;4000;5000;6000
2;"PIM5231";1060;5000;10000;10000;217000;213000;262000;229000;254000;217000;164000;190000;128000;1000;2000;3000;4000;5000;6000
3;"PIM5231";1120;10;0;0;0;0;0;0;30000;10000;10000;10000;0;0;0;0;0;0;0
4;"PIM5231";1170;0;0;0;0;44620;44582;23840;60000;50000;50000;10000;100000;0;0;0;0;0;0
5;"PIM5231";1200;1111111;1000;2000;10000;0;5000;1000;10000;1000;3500;5730;0;0;0;0;0;0;4500
140;"PIM5244";1000;0;0;0;0;0;0;0;0;0;0;0;0;1000;2000;3000;4000;5000;6000
141;"PIM5244";1060;5000;10000;10000;217000;213000;262000;229000;254000;217000;164000;190000;128000;1000;2000;3000;4000;5000;6000
142;"PIM5244";1120;10;0;0;0;0;0;0;30000;10000;10000;10000;0;0;0;0;0;0;0
143;"PIM5244";1170;0;0;0;0;44620;44582;23840;60000;50000;50000;10000;100000;0;0;0;0;0;0
144;"PIM5244";1200;0;1000;2000;10000;0;5000;1000;10000;1000;3500;5730;0;0;0;0;0;0;4500

Ergebnis der obigen Abfrage:

Code:

"position";"posbez";"JAN";"FEB";"MAR";"APR";"MAI";"JUN";"JUL";"AUG";"SEP";"OKT";"NOV";"DEZ"
"1060";"*Umsatz Projekte incl.F&E";"0";"0";"0";"2000000";"1131644";"855564";"543500";"1016000";"868000";"656000";"760000";"512000"
"1120";"*Umsatz Wartung";"0";"0";"0";"0";"0";"0";"0";"120000";"40000";"40000";"40000";"0"
"1170";"*Umsatz Beratung";"0";"0";"0";"0";"178480";"178328";"95360";"240000";"200000";"200000";"40000";"400000"
"1200";"Seminare";"0";"0";"0";"0";"0";"0";"0";"40000";"4000";"14000";"22920";"0"

Ein "SELECT DISTINCT ..." bringt auch nichts, da die Anzahl der zurückgelieferten Datensätze i.O. ist - in diesem Beispiel hier 4.

Was ich machen möchte ist folgendes:

- schaue in "positionen" nach, welche "posid"s gehören zur Gruppe "pgid" 2000
- suche mir für JAN-JUL die Summe der Monatswerte aus den Ist-Daten raus, die mit den posids übereinstimmen, für die PAGIDS 5231 und 5244
- suche mir für AUG-DEZ die Summe der Monatswerte aus den Prognose-Daten raus, die mit den posids übereinstimmen, für die PAGIDS 5231 und 5244

Ich kann den Fehler soweit eingrenzen, dass es daran liegt, dass ich auf 2 Tabellen (Prognose und Ist) zugreife. Lasse ich das Statement nur über die Prognose-Daten oder nur über die Ist-Daten laufen, kommen die korrekten Werte für die Summen raus.

Gruß Igotcha

[Jelly]

Hallo,

schon mal folgedendes Statement probiert:

SQL-Code:

SELECT i.position, pos.posbez,
SUM(i.jan) AS JAN, SUM(i.FEB) AS FEB, SUM(i.MAR) AS MAR, SUM(i.APR) AS APR, SUM(i.MAI) AS MAI, SUM(i.JUN) AS JUN,
SUM(i.JUL) AS JUL, SUM(p.AUG) AS AUG, SUM(p.SEP) AS SEP, SUM(p.OKT) AS OKT, SUM(p.NOV) AS NOV, SUM(p.DEZ) AS DEZ
FROM daten_ist i, daten_prognose p, positionen pos
WHERE i.position=p.position AND i.position=pos.posid and p.position = pos.posid
AND i.pagid IN ("PIM5231","PIM5244")
AND pos.pgid=2000
GROUP BY i.position,pos.posbez

(ungestet)

[Igotcha]

Nein, leider immer noch das gleiche Problem.

Leider stoße ich mit meinen SQL-Kenntnissen hier an meine Grenze. Ich kann mir nur vorstellen, dass durch diese "Überkreuz-Zusammenstellung" des Ergebnisses eine doppelte Menge generiert wird. Wie ich dies aber verhindere, dafür habe ich keinen Ansatz.

Diese Abfrage ist für mich aber sehr wichtig und ich hoffe nicht, dass dies in dieser Form evtl. gar nicht umsetzbar ist...

Gruß Kay

P.S. Ich kann diese 3 Tabelle auch per Mail schicken, wenn sich jemand die Mühe machen möchte, live zu probieren.

[Hansa]

Meine Vermutung 8) :

pagid IN ("PIM5231","PIM5244") Mache mal da einen der beiden Werte weg, ob sich dann was ändert.

[Igotcha]

Zitat von Hansa:

Meine Vermutung 8) :

pagid IN ("PIM5231","PIM5244") Mache mal da einen der beiden Werte weg, ob sich dann was ändert.

Ja na klar ändert sich da was, was ja auch nur sinnvoll und so gewollt ist Wenn ich allerdings nur einen Wert drin habe, sind die Summen im Ergebnis auch verdoppelt. Bereinigt wird das, wenn ich die Tabelle daten_prognose aus der FROM-clause wegnehme (s.a. unten für den Normalfall, der ja funktioniert).

Nochmal kurz zum Tabellendesign:

Ich habe 2 Tabellen mit Daten: ISTWERTE und PROGNOSEWERTE und eine Tabelle mit Beziehungen, welches Umsatz-/Kostenarten zu welcher Umsatz-/Kostengruppe gehören. Hier am Beispiel ist "2000" die Umsatzgruppe "Umsatz ohne interne Verrechnung" und dazu gehören die Umsatzarten "Umsatz Beratung, Umsatz Projekte, etc.".

In der einen Tabelle stehen von allen Kostenstellen einmal die Istwerte und in der anderen von allen Kostenstellen die Prognosewerte drin.

In meinem Programm gibt es 2 Sichten auf die Daten:

Der Kostenstellenverantwortliche kann sich seine Prognosewerte anschauen und es gibt eine gemixte Sicht, die die bisher vorliegenden Istwerte und "auffüllend" die Prognosewerte bis zum Jahresende anzeigt - auf dieser Ebene funktioniert die entsprechende SQL-Abfrage auch.

Jetzt gibt es aber aber auch Geschäftsbereichsleiter, die es interessiert, welche Zahlen der Geschäftsbereich hat und diese setzen sich aus den Kostenstellen zusammen.

Für die Prognosesicht funktioniert ein analoges SQL-Statement, für diese gemixte Ist/Prognose-Sicht leider nicht.

Zu dem IN-Statement: Ein Geschäftsbereich hat x Kostenstellen. Genau diese x Kostenstellen sind in der DB hinterlegt und werden im IN-Statement abgefragt.

Hier ist das SQL-Statement für die Aggregation der PROGNOSEWERTE, die fehlerfrei funktioniert:

Delphi-Quellcode:

frmMain.ZQPOS.SQL.Add('SELECT p.posid, p.posbez, ');
frmMain.ZQPOS.SQL.Add('SUM(JAN) AS JAN, SUM(FEB) AS FEB, SUM(MAR) AS MAR, SUM(APR) AS APR, SUM(MAI) AS MAI, SUM(JUN) AS JUN, SUM(JUL) AS JUL, SUM(AUG) AS AUG, SUM(SEP) AS SEP, SUM(OKT) AS OKT, SUM(NOV) AS NOV, SUM(DEZ) AS DEZ, ');
frmMain.ZQPOS.SQL.Add('SUM(JAN_1) AS JAN_1, SUM(FEB_1) AS FEB_1, SUM(MAR_1) AS MAR_1, SUM(APR_1) AS APR_1, SUM(MAI_1) AS MAI_1, SUM(JUN_1) AS JUN_1 ');
frmMain.ZQPOS.SQL.Add('FROM positionen p, daten_prognose i ');
frmMain.ZQPOS.SQL.Add(Format('WHERE p.pgid=:pgid AND p.posid=i.position AND i.pagid IN (%s) ', [strDataID]));
frmMain.ZQPOS.SQL.Add('GROUP BY p.posid');

Und hier das SQL-Statement für die gemixte Sicht (Ist/Prognose) auf Kostenstellenebene (also nur für eine Kostenstelle), welche auch fehlerfrei funktioniert:

Delphi-Quellcode:

ZQPOS.SQL.Add('SELECT DISTINCT i.position, pos.posbez, ');
ZQPOS.SQL.Add(strIstPlan);
ZQPOS.SQL.Add('FROM daten_ist i, daten_prognose p, positionen pos ');
ZQPOS.SQL.Add('WHERE i.position=p.position AND i.position=pos.posid ');
ZQPOS.SQL.Add('AND i.pagid=:pagid AND pos.pgid=:pgid');

(in strIstPlan steht die generierte Monatsauswahl Plan/Ist, wie im ersten Post drin)

Wie gesagt, vermute ich, dass durch die Tabellenkreuzung im ursprünglichen Post irgendwas doppelt durchlaufen wird.

Gruß Igotcha

[MarkusB]

Hi Igotcha!

Zitat von Igotcha:

Wie gesagt, vermute ich, dass durch die Tabellenkreuzung im ursprünglichen Post irgendwas doppelt durchlaufen wird.

Du hast absolut recht! Richtung deiner Untersuchungen stimmt!

Ich habe deine Tabellen nachgebildet und festgestellt das die Abfrage falsche Ergebnisse liefert.
Leider im Moment kann ich nicht veiter forschen .

Viele Grüße
Markus

[Igotcha]

Danke für das Engagement!

Zitat von MarkusB:

Ich habe deine Tabellen nachgebildet und festgestellt das die Abfrage falsche Ergebnisse liefert.

Aber ich vermute eher, dass die falsche Abfrage richtige Ergebnisse liefert

[Hansa]

Was soll eine Tabellenkreuzung sein ? Die Ursache wird hier irgendwo liegen :
'WHERE p.pgid=:pgid AND p.posid=i.position AND i.pagid IN (%s) ' Lasse doch mal die ganzen Summen weg (sprich : Problemstelle verkleinern) und guck, welche Daten dann überhaupt geliefert werden. Ich könnte wetten, daß sie da schon doppelt sind. Insofern wäre es kein Wunder, wenn auch die Summe doppelt wird. 8)

[Igotcha]

Hmmm, ich habe mal folgendes Statement durchlaufen lassen:

Code:

SELECT *
FROM daten_ist i, daten_prognose p, positionen pos
WHERE i.position=pos.posid AND p.position=pos.posid
AND i.pagid IN ("PIM5244")
AND pos.pgid=2000
GROUP BY i.position

Und das Ergebnis verstehe ich überhaupt nicht:

Code:

Id;PAGID;POSITION;APR;MAI;JUN;JUL;AUG;Id;PAGID;POSITION;JAN;FEB;Id;posid;posbez;pgid
141;PIM5244;1060;1000000;282911;213891;135875;-133577;2;PIM5231;1060;5000;10000;2;1060;*Umsatz Projekte incl.F&E;2000
142;PIM5244;1120;0;0;0;0;0;3;PIM5231;1120;10;0;3;1120;*Umsatz Wartung;2000
143;PIM5244;1170;0;44620;44582;23840;0;4;PIM5231;1170;0;0;4;1170;*Umsatz Beratung;2000
144;PIM5244;1200;0;0;0;0;0;5;PIM5231;1200;1111111;1000;5;1200;Seminare;2000
145;PIM5244;1250;0;0;0;0;0;6;PIM5231;1250;0;0;6;1250;Lizenzen Eigen;2000
146;PIM5244;1300;0;0;0;0;0;7;PIM5231;1300;0;0;7;1300;Fremdlizenzen Eigen;2000
147;PIM5244;1350;0;0;0;0;0;8;PIM5231;1350;0;0;8;1350;Fremdlizenzen Sonstige;2000
148;PIM5244;1400;0;0;0;0;0;9;PIM5231;1400;0;0;9;1400;Hardware & andere;2000
149;PIM5244;1450;0;2373;5903;2781;0;10;PIM5231;1450;0;0;10;1450;Reisen;2000

Zumindest sieht man hier erstmal, dass trotz der Bedingung "PIM5244" auch "PIM5231"-Werte angezeigt werden.

Wenn ich jetzt als Bedingung "IN ("PIM5231") in dem Statement stehen habe, kommen im Ergebnis "PIM5244"-Werte gar nicht mehr vor.

P.S. Ich habe die Tabellen als SQL-Script beigefügt.

[Hansa]

Für mich sieht es aber schon logisch aus. Vorne steht immer "PIM5244" und das kommt hierdurch :

AND i.pagid IN ("PIM5244") Dann mache jetzt weiter mit der Reduzierung des Problems : WHERE-Klausel verkleinern !! Solange, bis ein Ergebnis rauskommt, welches nachvollziehbar ist. Und dann wieder anders rum : WHERE-Klausel Schritt für Schritt wieder erweitern und dann zum Schluß wieder die Summen. Bei komplexen Problemen gehts manchmal nicht anders. Und nicht vergessen : unbeding ausreichend kommentieren! Später wirst du das Problem eventuell wieder haben und weißt mit Sicherheit nicht mehr warum du was wo gemacht hast!!!