Delphi-PRAXiS

Delphi-PRAXiS (https://www.delphipraxis.net/forum.php)
-   Datenbanken (https://www.delphipraxis.net/15-datenbanken/)
-   -   Fehler bei Left Join (https://www.delphipraxis.net/208770-fehler-bei-left-join.html)

Ykcim 13. Sep 2021 11:50

Datenbank: MSSql • Version: 11 • Zugriff über: FireDac

Fehler bei Left Join
 
Hallo Zusammen,

ich habe eine Query über mehrere Tabelle mit Left Join. Eigentlich klappt auch alles. Allerdings ist jetzt nach ein paar Wochen aufgefallen, dass zwei Werte, die summiert werden, falsch sind.

Delphi-Quellcode:
SELECT    a.zynrefkl AS 'Artikelnummer',
         a.afg_oms1 AS 'Artikelbezeichnung 1',
         concat('VPE:',a.per__chk,' Stück') AS 'Artikelbezeichnung 2',
         '' AS 'Artikelbezeichnung 3',
         '' AS 'Artikelbezeichnung 4',
         '' AS 'Artikelbezeichnung 5',
         '' AS 'Artikelbezeichnung 6',
         b.in__vrrd AS 'Bestand',
         a.per__chk AS 'Abgreifmenge',
         a.minstock AS 'Mindestbestand',
         CASE WHEN b.in__vrrd < a.minstock then 'Mindestmenge unterschritten' ELSE '' END AS 'Bestandskommentar',
         '' AS 'Kommentar',
         COALESCE(SUM(c.b_aantal),0) AS 'Rückstand',
         COALESCE(SUM(d.aant_uit), 0) AS 'Gesamtverbrauch'
FROM afgart__ a
LEFT JOIN afgant__ b ON b.afg__ref = a.afg__ref
LEFT JOIN bstlyn__ c ON c.afg__ref = a.afg__ref AND c.levv_dat<GETDATE() AND c.lbn__ref = ''
LEFT JOIN hisafg__ d ON d.afg__ref = a.afg__ref
LEFT JOIN gegdet__ e on e.det__ref = a.afg__ref AND e.vrg__ref = '000002' and tabname_ = 'afgart__'
WHERE a.kla__rpn = 'MEYLE'
AND  a.zynrefkl <> ''
AND  (e.antw_txt <> '1' or e.antw_txt is NULL)
AND c.zynrefkl='108470001'
GROUP BY a.zynrefkl, a.afg_oms1, b.in__vrrd, a.per__chk, a.minstock
order by a.zynrefkl
Das Problem tritt durch die Zeile
Delphi-Quellcode:
LEFT JOIN hisafg__ d ON d.afg__ref = a.afg__ref
Ohne die Tabelle d wird der Rückstand (Tabelle C) korrekt berechnet. Aber mit der Tabelle D kommt eine viel zu hohe Summe für den Rückstand raus und auch der Gesamtverbrauch ist viel zu hoch.

Sieht jemand, was ich falsch mache?

Vielen Dank
Patrick

DeddyH 13. Sep 2021 13:12

AW: Fehler bei Left Join
 
Bist Du Dir sicher, dass ein LEFT (OUTER) JOIN hier die richtige Wahl ist? Was kommt denn bei einem INNER JOIN heraus?

Papaschlumpf73 13. Sep 2021 13:45

AW: Fehler bei Left Join
 
Wenn Tabelle hisafg__ d hier mehrere Treffer für einen Datensatz aus Tabelle a liefert (d.afg__ref = a.afg__ref) werden auch für alle anderen Tabellen so viele Datensätze wie Treffer in d angezeigt. Also wenn d 3 Treffer für einen Datensatz aus a hat, wird auch der eine Datensatz aus a, b, c usw. 3 mal angezeigt. Somit können Berechnungsergebnisse komplett aus dem Ruder geraten.

Ykcim 13. Sep 2021 13:53

AW: Fehler bei Left Join
 
Mit INNER Join habe ich leider das gleiche Ergebnis bekommen...

Ich versuche es gerade so zu lösen.

Delphi-Quellcode:
SELECT    a.zynrefkl AS 'Artikelnummer',
         a.afg_oms1 AS 'Artikelbezeichnung 1',
         concat('VPE:',a.per__chk,' Stück') AS 'Artikelbezeichnung 2',
         '' AS 'Artikelbezeichnung 3',
         '' AS 'Artikelbezeichnung 4',
         '' AS 'Artikelbezeichnung 5',
         '' AS 'Artikelbezeichnung 6',
         b.in__vrrd AS 'Bestand',
         a.per__chk AS 'Abgreifmenge',
         a.minstock AS 'Mindestbestand',
         CASE WHEN b.in__vrrd < a.minstock then 'Mindestmenge unterschritten' ELSE '' END AS 'Bestandskommentar',
         '' AS 'Kommentar',
      (SELECT COALESCE(SUM(c.b_aantal),0) FROM bstlyn__ c WHERE c.afg__ref=a.afg__ref
                                                          AND c.levv_dat<GETDATE()
                                                          AND c.lbn__ref = '') AS 'Rückstand',
      (SELECT COALESCE(SUM(d.aant_uit),0) FROM hisafg__ d WHERE d.afg__ref=a.afg__ref) AS 'Gesamtverbrauch'
FROM afgart__ as a
LEFT JOIN afgant__ b ON b.afg__ref = a.afg__ref
LEFT JOIN gegdet__ e on e.det__ref = a.afg__ref and vrg__ref = '000002' and tabname_ = 'afgart__'
WHERE a.kla__rpn = 'MEYLE'
AND  (e.antw_txt <> '1' or e.antw_txt is NULL)
AND  a.zynrefkl <> ''
AND a.zynrefkl='108470001'
GROUP BY a.zynrefkl, a.afg__ref, a.afg_oms1, b.in__vrrd, a.per__chk, a.minstock
order by a.zynrefkl
Das klappt zwar in HeidiSQL, aber leider zickt mein Programm damit rum...
Irgendwie stelle ich mich gerade ziemlich doof an...

Ykcim 13. Sep 2021 13:55

AW: Fehler bei Left Join
 
@PapaSchlumpf: Es gibt mehrere Datensätze in der Tabelle. Aber werden die nicht durch sie Summierung gruppiert?

Uwe Raabe 13. Sep 2021 14:09

AW: Fehler bei Left Join
 
Lass mal die SUM und GROUP BY Anweisungen weg und analysiere die tatsächlich zurückgegebenen Datensätze.

Delphi.Narium 13. Sep 2021 14:22

AW: Fehler bei Left Join
 
Was willst Du damit erreichen?
SQL-Code:
COALESCE(SUM(d.aant_uit),0)

Soll hier 0 geliefert werden, wenn die Summe aller d.aant_uit = Null ist oder soll hier, wenn d.aant_uit = null ist, bei der Summenbildung anstellte von Null 0 genommen werden?

Bei mir sähe es jedenfalls so aus:
SQL-Code:
SUM(COALESCE(d.aant_uit,0))
Je nach Datenbank kann es (soweit ich mich erinnere) passieren, dass die Summe mehrere Zahlen = null ist, wenn mindestens eine Zahl = Null ist.
Quasi sowas:
SQL-Code:
SUM(1 + 1 + 1) = 3
SUM(1 + Null + 1) = Null
Prüfe bitte mal nach, wie sich MSSql in einer derartigen Situation verhält.

Papaschlumpf73 13. Sep 2021 14:46

AW: Fehler bei Left Join
 
Zitat:

Zitat von Uwe Raabe (Beitrag 1494694)
Lass mal die SUM und GROUP BY Anweisungen weg und analysiere die tatsächlich zurückgegebenen Datensätze.

Das ist eine gute Idee. Du wirst sehen, dass die Datensätze doppelt und dreifach sind. Und SUM() rechnet die dann auch doppelt und dreifach zusammen. GROUP BY bezieht sich nur auf die Datenfelder, die nicht summiert werden.

Ykcim 13. Sep 2021 15:30

AW: Fehler bei Left Join
 
Also es ist tatsächlich so, dass ich dann jeden Datensatz 4 mal bekomme...
Kann ich beim LEFT JOIN irgendwie gruppieren?

Ich habe gerade bemerkt, dass nur die Kombination aus
Delphi-Quellcode:
LEFT JOIN bstlyn__ c ON c.afg__ref = a.afg__ref AND c.levv_dat<GETDATE() AND c.lbn__ref = ''
und
Delphi-Quellcode:
Left JOIN hisafg__ d ON d.afg__ref = a.afg__ref
zu Fehlern führt.

Egal welches ich entferne, das andere wird dann korrekt berechnet...

Blup 13. Sep 2021 15:55

AW: Fehler bei Left Join
 
In #4 warst du schon auf dem richtigen Weg.
Allerdings wurde dort plötzlich die Bedingung verändert:
Code:
AND a.zynrefkl <> ''
AND (e.antw_txt <> '1' or e.antw_txt is NULL)
AND c.zynrefkl='108470001'
Code:
AND (e.antw_txt <> '1' or e.antw_txt is NULL)
AND a.zynrefkl <> ''
AND a.zynrefkl='108470001'
"a.zynrefkl='108470001'" ist falsch.
Die Bedingung "c.zynrefkl='108470001'" gehört in die Abfrage von c.
Zumindest wenn das orginale SQL richtig war.

Ykcim 13. Sep 2021 16:10

AW: Fehler bei Left Join
 
Das
Delphi-Quellcode:
a.zynrefkl='108470001'
hat eigentlich nichts in der Abfrage zu suchen. Ich schränke die Abfrage damit nur testweise auf einen Artikel ein. Die Abfrage mit den beiden Selects bringt mir das richtige Ergebnis, aber wenn ich es in mein Delphi-Programm einbaue, bekomme ich eine Fehlermeldung, die ich bis jetzt nicht lösen konnte...:
Delphi-Quellcode:
Erste Gelegenheit für Exception bei $76BBB512. Exception-Klasse EMSSQLNativeException mit Meldung '[FireDAC][Phys][ODBC][Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near 'Rückstand'.'. Prozess BD_DHL_Srvr.exe (12540)
Ich kann aber nicht erkennen, woran es liegt. Wenn ich den SQL-String in HeidiSQL ausführe, klappt er...

Delphi-Quellcode:
      Query.SQL.Add('SELECT  a.zynrefkl AS ' + QuotedStr('Artikelnummer') + ', '+
                             'a.afg_oms1 AS ' + QuotedStr('Artikelbezeichnung 1') + ', '+
                             'concat('+QuotedStr('VPE:')+',a.per__chk,'+QuotedStr(' Stück')+') AS '+QuotedStr('Artikelbezeichnung 2')+' , '+
                             QuotedStr('') +' AS ' + QuotedStr('Artikelbezeichnung 3')+', '+
                             QuotedStr('') +' AS '+QuotedStr('Artikelbezeichnung 4')+', '+
                             QuotedStr('') +' AS '+QuotedStr('Artikelbezeichnung 5')+', '+
                             QuotedStr('') +' AS '+QuotedStr('Artikelbezeichnung 6')+', '+
                             'b.in__vrrd AS ' + QuotedStr('Bestand') + ', '+
                             'a.per__chk AS ' + QuotedStr('Abgreifmenge') + ', '+
                             'a.minstock AS ' + QuotedStr('Mindestbestand') + ', '+
                             'CASE WHEN b.in__vrrd < a.minstock then '+QuotedStr('Mindestmenge unterschritten') + ' ELSE '+QuotedStr('') + ' END AS '+QuotedStr('Bestandskommentar') + ', '+
                             QuotedStr('') + ' AS '+ QuotedStr('Kommentar') + ', '+
                             '(SELECT COALESCE(SUM(c.b_aantal),0) FROM bstlyn__ c '+
                                    'WHERE c.afg__ref=a.afg__ref '+
                                    'AND c.levv_dat<GETDATE() '+
                                    'AND c.lbn__ref = '') AS ' + QuotedStr('Rückstand')+', '+
                             '(SELECT COALESCE(SUM(d.aant_uit),0) FROM hisafg__ d '+
                                    'WHERE d.afg__ref=a.afg__ref) AS '+QuotedStr('Gesamtverbrauch')+' '+
                    ' FROM afgart__ a '+
                    'LEFT JOIN afgant__ b ON b.afg__ref = a.afg__ref '+
                    'LEFT JOIN gegdet__ e on e.det__ref = a.afg__ref and vrg__ref = '+QuotedStr('000002') + ' and tabname_ = '+QuotedStr('afgart__') + ' '+
                    'WHERE a.kla__rpn = '+QuotedStr('MEYLE')+' '+
                    'AND  a.zynrefkl <> ' + QuotedStr('') + ' '+
                    'AND  (e.antw_txt <> '+ QuotedStr('1')+' '+
                           'or e.antw_txt is null) '+
                    'GROUP BY a.zynrefkl, a.afg_oms1, b.in__vrrd, a.per__chk, a.minstock, a.afg__ref '+
                    'order by a.zynrefkl');
      //Query.ParamByName('Kunde').AsString := 'MEYLE';
      //Query.ParamByName('KZeichenAuslauf').AsString := '1';
      ExecQuery(Query, Cols, Rows, 0);
      //Gesamtverbrauch Stand 12.04.2021 holen
      MyQuery.sql.Add('select * from stand_20210412');
      ExecQuery(MyQuery, MyCols, MyRows, 0);
Keine Ahnung, ich brauch doch nur eine funktionierende Lösung...

Uwe Raabe 13. Sep 2021 16:19

AW: Fehler bei Left Join
 
Probier doch mal 'Rueckstand' anstatt 'Rückstand'...

Ykcim 13. Sep 2021 16:20

AW: Fehler bei Left Join
 
Leider kein Erfolg...
Delphi-Quellcode:
Erste Gelegenheit für Exception bei $76BBB512. Exception-Klasse EMSSQLNativeException mit Meldung '[FireDAC][Phys][ODBC][Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near 'Rueckstand'.'. Prozess BD_DHL_Srvr.exe (18912)

Uwe Raabe 13. Sep 2021 16:27

AW: Fehler bei Left Join
 
Dann wäre es sinnvoll, den Inhalt von SQL nach dem Add mal zu inspizieren.

Achim Kalwa 13. Sep 2021 17:02

AW: Fehler bei Left Join
 
Der Fehler ist hier:
Delphi-Quellcode:
 'AND c.lbn__ref = '') AS ' + QuotedStr('Rückstand')+', '+

Die zwei Hochkomma werden als nur ein Zeichen in den SQL-Text geschrieben!

Um also zwei Hochkommata zu erhalten, musst Du das Zeichen viermal schreiben:
Delphi-Quellcode:
 'AND c.lbn__ref = '''') AS ' + QuotedStr('Rückstand')+', '+

Ykcim 13. Sep 2021 17:12

AW: Fehler bei Left Join
 
DANKE!!!

Jetzt tut es was es soll - ein Tag Arbeit für so eine Dusseligkeit.

Vielen Dank an alle, die mitgeholfen haben!!!

Uwe Raabe 13. Sep 2021 17:17

AW: Fehler bei Left Join
 
Warum verwendest du keine Parameter und Makros? Das würde helfen, solche Fehler zu vermeiden.

Ykcim 13. Sep 2021 18:53

AW: Fehler bei Left Join
 
Makros weiß ich nicht, was Du damit meinst. Mit Parametern arbeite ich immer nur, wenn sie variable sein können.

Vielen Dank
Patrick

Delphi.Narium 13. Sep 2021 19:45

AW: Fehler bei Left Join
 
Vermutlich sollte Dein SQL in etwa so aussehen, damit es per HeidiSQL oder sonsteiner Datenbankoberfläche ausführbar ist.
SQL-Code:
SELECT
  a.zynrefkl AS 'Artikelnummer',
  a.afg_oms1 AS 'Artikelbezeichnung 1',
  concat('VPE:',a.per__chk,' Stück') AS 'Artikelbezeichnung 2',
  '' AS 'Artikelbezeichnung 3',
  '' AS 'Artikelbezeichnung 4',
  '' AS 'Artikelbezeichnung 5',
  '' AS 'Artikelbezeichnung 6',
  b.in__vrrd AS 'Bestand',
  a.per__chk AS 'Abgreifmenge',
  a.minstock AS 'Mindestbestand',
  CASE WHEN b.in__vrrd < a.minstock THEN
    'Mindestmenge unterschritten'
  ELSE
    ''
  END AS 'Bestandskommentar',
  '' AS 'Kommentar',
  (
    SELECT SUM(COALESCE(c.b_aantal,0))
    FROM bstlyn__ c
    WHERE c.afg__ref = a.afg__ref
    AND c.levv_dat < GETDATE()
    AND c.lbn__ref = ''
  ) AS 'Rückstand',
  (
    SELECT SUM(COALESCE(d.aant_uit,0))
    FROM hisafg__ d
    WHERE d.afg__ref = a.afg__ref
  ) AS 'Gesamtverbrauch'
FROM afgart__ a
LEFT JOIN afgant__ b ON b.afg__ref = a.afg__ref
LEFT JOIN gegdet__ e ON e.det__ref = a.afg__ref AND vrg__ref = '000002' AND tabname_ = 'afgart__'
WHERE a.kla__rpn = 'MEYLE'
AND a.zynrefkl <> ''
AND (e.antw_txt <> '1' OR e.antw_txt IS NULL)
GROUP BY a.zynrefkl, a.afg_oms1, b.in__vrrd, a.per__chk, a.minstock, a.afg__ref
ORDER BY a.zynrefkl
Um daraus nun kompilierbaren Delphiquelltext zu erhalten benötigt man ein TMemo (WordWrap := false), einen TButton und folgende Routine:
Delphi-Quellcode:
procedure TForm1.Button1Click(Sender: TObject);
var
  i : Integer;
begin
  for i := 0 to Memo1.Lines.Count - 1 do Memo1.Lines[i] := Format(' Query.SQL.Add(%s);',[QuotedStr(Memo1.Lines[i])]));
  Memo1.SelectAll;
  Memo1.CopyToClipboard;
end.
Als Ergebnis erhält man den Quelltext, den man dann einfach in der IDE an der passenden Stelle (per Copy&Paste) einfügt:
Delphi-Quellcode:
  Query.SQL.Add('SELECT');
  Query.SQL.Add(' a.zynrefkl AS ''Artikelnummer'',');
  Query.SQL.Add(' a.afg_oms1 AS ''Artikelbezeichnung 1'',');
  Query.SQL.Add(' concat(''VPE:'',a.per__chk,'' Stück'') AS ''Artikelbezeichnung 2'',');
  Query.SQL.Add(' '''' AS ''Artikelbezeichnung 3'',');
  Query.SQL.Add(' '''' AS ''Artikelbezeichnung 4'',');
  Query.SQL.Add(' '''' AS ''Artikelbezeichnung 5'',');
  Query.SQL.Add(' '''' AS ''Artikelbezeichnung 6'',');
  Query.SQL.Add(' b.in__vrrd AS ''Bestand'',');
  Query.SQL.Add(' a.per__chk AS ''Abgreifmenge'',');
  Query.SQL.Add(' a.minstock AS ''Mindestbestand'',');
  Query.SQL.Add(' CASE WHEN b.in__vrrd < a.minstock THEN');
  Query.SQL.Add('   ''Mindestmenge unterschritten''');
  Query.SQL.Add(' ELSE');
  Query.SQL.Add('   ''''');
  Query.SQL.Add(' END AS ''Bestandskommentar'',');
  Query.SQL.Add(' '''' AS ''Kommentar'',');
  Query.SQL.Add(' (');
  Query.SQL.Add('   SELECT SUM(COALESCE(c.b_aantal,0))');
  Query.SQL.Add('   FROM bstlyn__ c');
  Query.SQL.Add('   WHERE c.afg__ref = a.afg__ref');
  Query.SQL.Add('   AND c.levv_dat < GETDATE()');
  Query.SQL.Add('   AND c.lbn__ref = ''''');
  Query.SQL.Add(' ) AS ''Rückstand'',');
  Query.SQL.Add(' (');
  Query.SQL.Add('   SELECT SUM(COALESCE(d.aant_uit,0))');
  Query.SQL.Add('   FROM hisafg__ d');
  Query.SQL.Add('   WHERE d.afg__ref = a.afg__ref');
  Query.SQL.Add(' ) AS ''Gesamtverbrauch''');
  Query.SQL.Add('FROM afgart__ a');
  Query.SQL.Add('LEFT JOIN afgant__ b ON b.afg__ref = a.afg__ref');
  Query.SQL.Add('LEFT JOIN gegdet__ e ON e.det__ref = a.afg__ref AND vrg__ref = ''000002'' AND tabname_ = ''afgart__''');
  Query.SQL.Add('WHERE a.kla__rpn = ''MEYLE''');
  Query.SQL.Add('AND a.zynrefkl <> ''''');
  Query.SQL.Add('AND (e.antw_txt <> ''1'' OR e.antw_txt IS NULL)');
  Query.SQL.Add('GROUP BY a.zynrefkl, a.afg_oms1, b.in__vrrd, a.per__chk, a.minstock, a.afg__ref');
  Query.SQL.Add('ORDER BY a.zynrefkl');
Dürfte vermutlich schneller gehen, als alles per dutzender QuotedStr und vielen + als Einzeiler mit nur einem Query.SQL.Add irgendwie, hoffentlich kompilierbar, hinzubekommen und dann auch noch ein ausführbares SQL zu erhalten. Und das Nachzählen der ' kann dann auch entfallen ;-)

Auswand: 15 oder 30 Minuten?
(Einmalig und nicht bei jedem SQL auf's Neue ;-))

Uwe Raabe 14. Sep 2021 00:40

AW: Fehler bei Left Join
 
Zitat:

Zitat von Ykcim (Beitrag 1494717)
Makros weiß ich nicht, was Du damit meinst.

Makros sind ein Feature von FireDAC mit dem man auch Teile von SQL Anweisungen parametrieren kann, die mit Parametern nicht gehen (z.B. Tabellen- und Feldnamen).

Zitat:

Zitat von Ykcim (Beitrag 1494717)
Mit Parametern arbeite ich immer nur, wenn sie variable sein können.

Neben der Variabilität haben Parameter auch den Vorteil, dass man sich nicht um Quotes und sonstige Formatbesonderheiten kümmern muss.

Blup 14. Sep 2021 11:06

AW: Fehler bei Left Join
 
Für Anwendungsfälle mit komplexen Abfragen macht es Sinn das SQL als View auf dem Server anzulegen.
In der Anwendung steht dann z.B. nur:
Code:
select * from view where ...
Die Zuständigkeit DB-Entwickler/Anwendungsentwickler lässt sich abgrenzen.
Das SQL kann auch ohne die Anwendung getestet werden.
Spätere Änderungen/Optimierung in der DB müssen keine Änderungen in der Anwendung nach sich ziehen.
Wenn doch ist zumindest klar welche Anwendungsfälle betroffen sind.


Alle Zeitangaben in WEZ +1. Es ist jetzt 11:54 Uhr.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
LinkBacks Enabled by vBSEO © 2011, Crawlability, Inc.
Delphi-PRAXiS (c) 2002 - 2023 by Daniel R. Wolf, 2024 by Thomas Breitkreuz