Delphi-PRAXiS - Delphi [SQL] Problem mit doppelter Ergebnismenge

Delphi-PRAXiS (https://www.delphipraxis.net/forum.php)

- Datenbanken (https://www.delphipraxis.net/15-datenbanken/)

- - Delphi [SQL] Problem mit doppelter Ergebnismenge (https://www.delphipraxis.net/31235-%5Bsql%5D-problem-mit-doppelter-ergebnismenge.html)

[SQL] Problem mit doppelter Ergebnismenge

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

Re: [SQL] Problem mit doppelter Ergebnismenge

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)

Re: [SQL] Problem mit doppelter Ergebnismenge

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.

Re: [SQL] Problem mit doppelter Ergebnismenge

Meine Vermutung 8) :

SQL-Code:

pagid IN ("PIM5231","PIM5244")

Mache mal da einen der beiden Werte weg, ob sich dann was ändert.

Re: [SQL] Problem mit doppelter Ergebnismenge

Zitat:

Zitat von Hansa

Meine Vermutung 8) :

SQL-Code:

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

Re: [SQL] Problem mit doppelter Ergebnismenge

Hi Igotcha!

Zitat:

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
:gruebel:

Re: [SQL] Problem mit doppelter Ergebnismenge

Danke für das Engagement!

Zitat:

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 ;-)

Re: [SQL] Problem mit doppelter Ergebnismenge

Was soll eine Tabellenkreuzung sein ? :shock: Die Ursache wird hier irgendwo liegen :

SQL-Code:

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

Re: [SQL] Problem mit doppelter Ergebnismenge

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. :gruebel:

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

Re: [SQL] Problem mit doppelter Ergebnismenge

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

SQL-Code:

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

Re: [SQL] Problem mit doppelter Ergebnismenge

Hi Igotcha!

Dein Problem laesst sich mit Hilfe 3 Views loesen:

1. VIEW1 : Summen pro Position aus der "DATEN_IST" Tabelle (daten_ist_summe_pro_position)

SQL-Code:

			SELECT position, sum(jan) AS jan0, 

                 sum(feb) AS feb0, 

                 sum(mar) AS mar0, 

                 sum(apr) AS apr0, 

                 sum(mai) AS mai0, 

                 sum(jun) AS jun0, 

                 sum(jul) AS jul0, 

                 sum(aug) AS aug0, 

                 sum(sep) AS sep0, 

                 sum(okt) AS okt0, 

                 sum(nov) AS nov0, 

                 sum(dez) AS dez0

FROM daten_ist, positionen

WHERE position=positionen.posid and pagid in ("PIM5231", "PIM5244") and positionen.pgid=2000

GROUP BY position;

Ergebnis siehe Anhang: view1.jpg

2. VIEW2 : Summen pro Position aus der "DATEN_PROGNOSE" Tabelle (daten_prognose_summe_pro_position)

SQL-Code:

			SELECT position, sum(jan) AS jan0, 

                 sum(feb) AS feb0, 

                 sum(mar) AS mar0, 

                 sum(apr) AS apr0, 

                 sum(mai) AS mai0, 

                 sum(jun) AS jun0, 

                 sum(jul) AS jul0, 

                 sum(aug) AS aug0, 

                 sum(sep) AS sep0, 

                 sum(okt) AS okt0, 

                 sum(nov) AS nov0, 

                 sum(dez) AS dez0, 

                 sum(jan_1) AS jan1, 

                 sum(feb_1) AS feb1, 

                 sum(mar_1) AS mar1, 

                 sum(apr_1) AS apr1, 

                 sum(mai_1) AS mai1, 

                 sum(jun_1) AS jun1

FROM daten_prognose, positionen

WHERE position=positionen.posid and pagid in ("PIM5231", "PIM5244") and positionen.pgid=2000

GROUP BY position;

Ergebnis siehe Anhang: view2.jpg

3. View3: Summen pro Position aus VIEW1 und VIEW2

SQL-Code:

			SELECT i.position, i.jan0 as jani, 

                   i.feb0 as febi, 

                   i.mar0 as mari, 

                   i.apr0 as apri, 

                   i.mai0 as maii, 

                   i.jun0 as juni, 

                   i.jul0 as juli, 

                   p.aug0 as augp, 

                   p.sep0 as sepp, 

                   p.okt0 as oktp, 

                   p.nov0 as novp, 

                   p.dez0 as dezp

FROM daten_ist_summe_pro_position AS i, daten_prognose_summe_pro_position AS p

WHERE i.position=p.position;

Ergebnis siehe Anhang: view3.jpg

Viele Grüße
Markus
:gruebel:

Re: [SQL] Problem mit doppelter Ergebnismenge

Hi Markus,

das sieht im Ergebnis schon gut aus :-)
(wobei ich mich frage, wie im Bild 3 ein "apr0" zustande kommt)

Auf Kostenstellenebene (also nur 1) erfolgt die Generierung meiner Ansicht (die ja auch funktioniert) in 1-2 Sekunden. Deine Lösung sieht mir da schon nach mehr aus.

Nur, wie setze ich das performant im Programm um?

Hättest Du da evtl. einen Codeschnipsel, wie man Views erzeugt und diese verwendet?

Danke und Gruß
Igotcha

EDIT: Habe gerade gesehen, dass es das "CREATE"-Kommando erst seit 5.0.1 gibt und das ist ein aktuelles Snapshot Release - naja, mal updaten ;-)

Re: [SQL] Problem mit doppelter Ergebnismenge

Hi Igotcha!

Leider habe ich kein Codeschnipsel, den Du als fertige Lösung verwendet könntest.
Du kannst aber einfach jeweils eine TQery für jede SELECT-Anweisung erstellen, etwa so:

Delphi-Quellcode:

			...

Var

    ist_data: TQuery;

    prognose_data: TQuery;

    result_data: TQuery;

    ...

begin

    ...

    ist_data.SQL.Clear;

    ist_data := TQuery.Create(self);

    ist_data.SQL.Add('select … ');

    ist_data.SQL.Add('from ist_data … ');

    ist_data.Prepare;

    ist_data.Open;

    ...

    prognose_data.SQL.Clear;

    prognose_data := TQuery.Create(self);

    prognose_data.SQL.Add('select … ');

    prognose_data.SQL.Add('from prognose_data … ');

    prognose_data.Prepare;

    prognose_data.Open;

    ...

    result_data.SQL.Clear;

    result_data := TQuery.Create(self);

    result_data.SQL.Add('select … ');

    result_data.SQL.Add('from ist_data, prognose_data … ');

    result_data.Prepare;

    result_data.Open;

    ...

Dadurch erstellst du “Views”, die du weiter verarbeiten könntest.

Und am Ende:

Delphi-Quellcode:

			    ...

    ist _query.Close;

    ist _query.Destroy;

    prognose _query.Close;

    prognose _query.Destroy;

    result _query.Close;

    result_query.Destroy;

    ...

Kleine Unreinheit: "apr0" soll "apri" heißen (sorry). :oops:

Viele Grüße
Markus
:gruebel:

Re: [SQL] Problem mit doppelter Ergebnismenge

Danke Dir.

Ich habe gerade die neueste mysql installiert und das "CREATE VIEW" funktioniert nun. Ich muss jetzt mal sehen, was performanter ist. Wie Du evtl. aus der Tabelle "positionen" sehen kannst, muß ich pro Sicht für ca. 20 Gruppen die Unterpositionen ermitteln - sprich als ca. 20 mal eben diese drei Schritte durchführen.

Werde am Wochenende mal ausführlich testen.

Gruß Igotcha