MySQL versus MsSQL Syntax Group By

[Ykcim]

Hallo Zusammen,

ich arbeite seit 15 Jahren immer nur mit MySQL-Datenbanken und muss jetzt ein Projekt auf MsSQL umsetzen. Das klappte bis jetzt sehr gut, aber ich stolper gerade über die Group BY Anweisung...

Delphi-Quellcode:

SELECT    a.zynrefkl AS 'Nr. beim Kunden',
         a.afg_oms1 AS 'Artikel-Bezeichnung',
         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 'Kommentar',
         SUM(c.b_aantal) AS 'Rückstand'
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.vrzvouur<GETDATE() AND c.vrz__tst = '1'
WHERE a.kla__rpn = 'LBBW'
GROUP BY a.zynrefkl

Ich habe dieses SQL-Statement und bekomme die Fehlermeldung:

Zitat:

Column ’afgart__.afg_oms1’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Ich habe leider keine für mich nachvollziehbare Lösung gefunden. Kann mir jemand dabei helfen?

Vielen Dank
Patrick

[mkinzler]

Es muss jede Spalte in der Feldliste entweder als Aggegat oder als Teil der Gruppierung vorliegen.
Bei Dir ist das eigentlich für keine der Spalten der Fall. Mich wundert, dass es bei MySQL funktioniert.

[Ykcim]

Ich habe gerade Schwierigkeiten, die Logik zu verstehen.

a.zynrefkl ist eine ArtikelNr. Ich möchte das Ergebnis der Summierung für jede ArtikelNr bekommen.
a.afg_oms1 das ist die Artikelbeschreibung. Die gibt es nur einmal. Ich verstehe nicht, was ich hier berechnen soll. Ich kann sie natürlich in die Group By Klausel hinzufügen, aber das hätte keinen Sinn, oder?
b.in__vrrd kommt einmal vor und hängt an der ArtikelNr.
a.per__chk ist die VE und wir aus der Zeile der gruppierten ArtikelNr genommen
a.minstock gilt das gleiche

Ich verstehe leider nicht, warum ich künstlich irgendetwas berechnen soll - eventuell damit ungewollte Effekte erziele - nur um etwas zu gruppieren. Ich wüsste nicht, wie ich das in meinem Fall machen sollte...

Ich habe es jetzt mal so versucht, muss aber das Erbegnis mühselig kontrollieren...

Delphi-Quellcode:

SELECT    a.zynrefkl AS 'Nr. beim Kunden',
         a.afg_oms1 AS 'Artikel-Bezeichnung',
         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 'Kommentar',
         SUM(c.b_aantal) AS 'Rückstand',
         SUM(d.aant_uit) 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.vrzvouur<GETDATE() AND c.vrz__tst = '1'
LEFT JOIN hisafg__ d ON d.afg__ref = a.afg__ref AND MONTH(d.datum___) = MONTH(GETDATE()) 
WHERE a.kla__rpn = 'LBBW'
GROUP BY a.zynrefkl, a.afg_oms1, b.in__vrrd, a.per__chk, a.minstock

[mkinzler]

Gruppieren heisst ja Zusammenfassen. Die Felder vo a kann man alle in die Gruppierung nehmen.
Wieviel Werte erzeugen die Joins jeweils?

[Ykcim]

b (afgant__ ) hat eine 1:1 Verbindung
C (bstlyn__) und d (hisafg__) geben mehrere Daten aus. Aber durch die Summierung werden daraus auch nur ein Datensatz...

Ich muss mich an diese Denkweise gewöhnen. Eigentlich packe ich immer so wenig wie möglich in die Gruppierung, weil ich mir einbilde, dass die Performance dann besser ist...

[mkinzler]

Eine andere Alternative wäre es Aggegate zu erzwingen und so aus einem einzelnen Wert ein Aggegat zu machen, das kostet aber sicherlich mehr als eine erweiterte Gruppierung.

[Delphi.Narium]

In "normalem" SQL wäre das wohl so erforderlich:

SQL-Code:

SELECT    a.zynrefkl AS 'Nr. beim Kunden',
         a.afg_oms1 AS 'Artikel-Bezeichnung',
         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 'Kommentar',
         SUM(c.b_aantal) AS 'Rückstand'
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.vrzvouur<GETDATE() AND c.vrz__tst = '1'
WHERE a.kla__rpn = 'LBBW'
GROUP BY 
  a.zynrefkl, 
  a.afg_oms1,
  b.in__vrrd,
  a.per__chk,
  a.minstock,
  'Kommentar'  -- da weiß ich jetzt garnicht, ob MS-SQL das so kann

Ins Group by muss schlicht und einfach alles, was nicht per Sum, Min, Max, ... zusammengefasst wird.

[Rolf Frei]

Könnte MS SQL nicht auch die Feldlistennummer verwenden, also "GROUP BY 1,2,3,4,5,6"? Ansonsten müsste er glaube ich auch die Resultfeldnamen verwenden, also nicht die zu grundeligenden DB-Feldnamen, also die deutschen AS Namen aus dem SQL.

[Delphi.Narium]

Eher nicht, das ist, soweit ich weiß, eine MySQL-Spezialität.

Ebenso wie die unvollständige Group By Liste, die nicht alle nichtaggregierten Spalten enthalten muss.

[Ykcim]

Aktuell verzweifle ich gerade. Eigentlich eine Sache von ein paar Minuten und sitze jetzt schon Stunden da dran...

Delphi-Quellcode:

SELECT    a.zynrefkl AS 'Artikelnummer',
         a.afg_oms1 AS 'Artikelbezeichnung 1',
         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.vrzvouur<GETDATE() AND c.vrz__tst = '1'
LEFT JOIN hisafg__ d ON d.afg__ref = a.afg__ref AND MONTH(d.datum___) = MONTH(GETDATE()) 
WHERE a.kla__rpn = 'LBBW'
AND    a.zynrefkl <> ''
GROUP BY   a.zynrefkl, 
         a.afg_oms1, 
         b.in__vrrd, 
         a.per__chk, 
         a.minstock

Das oben gepostete SQL-Statement funktioniert jetzt, wenn ich es mit HeidSQL ausführe. Ich bekomme 229 Datensätze angezeigt.

Wenn ich das Statement jetzt aber über meine Software absetze, bekomme ich nur 50 Datensätze raus...

Delphi-Quellcode:

procedure TDB_Modul.Get_WeeklyStock(AStream: TStream);
var      Logic: TLogic;
      Query: TFDQuery;
begin
   Logic := TLogic.create;
   Logic.Set_Query(Query, MSConnect);
   Try
      Query.SQL.Add('SELECT   a.zynrefkl AS ' + QuotedStr('Artikelnummer') + ', '+
                             'a.afg_oms1 AS ' + QuotedStr('Artikelbezeichnung 1') + ', '+
                             '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') + ', '+
                             'COALESCE(SUM(c.b_aantal),0) AS ' + QuotedStr('Rückstand') + ', '+
                             'COALESCE(SUM(d.aant_uit), 0) AS ' + QuotedStr('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.vrzvouur<GETDATE() AND c.vrz__tst = '+QuotedStr('1') + ' '+
                    'LEFT JOIN hisafg__ d ON d.afg__ref = a.afg__ref AND MONTH(d.datum___) = MONTH(GETDATE()) '+
                    'WHERE a.kla__rpn = ' + QuotedStr('LBBW') + ' '+
                    'AND   a.zynrefkl <> ' + QuotedStr('') + ' '+
                    'GROUP BY a.zynrefkl, a.afg_oms1, b.in__vrrd, a.per__chk, a.minstock ');
      Query.Open;
      if Assigned(AStream) then begin
         Query.SaveToStream(AStream, sfJSON);
      end;
   Finally
      Query.Free;
      Logic.Free;
   End;
end;

Es sind die ersten 50 Datensätze. Aber ich habe doch gar keine Limitierung eingestellt. Sieht jemand, woran das liegen könnte?

Vielen Dank
Patrick

[Delphi.Narium]

Erstmal nur zur Fehlereingrenzung:

Delphi-Quellcode:

   Query.Open;
   Query.Last;
   Query.First;

Ändert sich was?

Nicht alle Datenbankkomponenten laden grundsätzlich alle Datensätze, sondern nur die ersten X. (z. B. Attribut FetchRow bei den Zeoskomponenten.)

Was gibt Dir Query.RecordCount aus?

[Ykcim]

Interessant!
Nach Query.open ist der RecordCount bei 50,
nach Query.Last ist der RecordCount bei 229...

Das wäre also gelöst. Vielen Dank!

[Delphi.Narium]

Dieses Open-Last ist nur 'ne Krücke, die bestenfalls als suboptimal zu bezeichnen ist.

Gibt es bei Deiner Datenbankkomponenten ein Attribut, über das man sowas wie FetchRow, FirstRows, MaxRows o. ä. konfigurieren kann?

Das ist leider nicht bei allen Datenbankkomponenten einheitlich.

Bei ADO heißt es MaxRows, bei Zeos FetchRow, andernorts auch mal LoadLimit.

Schau bitte mal, ob Dir dashier weiterhilft: http://docwiki.embarcadero.com/RADSt...Rows_(FireDAC)

FetchOptions -> fmAll

[Uwe Raabe]

Wenn du alle Datensätze direkt nach dem Öffnen brauchst (z.B. wegen des SaveToStream), dann musst du bei der Query in FetchOptions das AutoFetchAll aktivieren.

Ups: zu spät

[Ykcim]

Ich nutze die TFDQuery Komponente. Ich habe diese Einstellungen versucht, aber leider keinen Erfolg gehabt:

Delphi-Quellcode:

Query.FetchOptions.AutoFetchAll;
   Query.FetchOptions.RowsetSize:=-1;

Die EInstellung RowSetSize schein standardmäßig auf 50 zu stehen. Das würde ja passen...

Ich dachte, der Wert -1 oder 0 würde ihn auf unendlich stellen, aber dann bekomme ich nur einen Datensatz.
Ich habe den Wert jetzt auf 99999999 gestellt, damit sollte ich bei der Anwendung auf der sicheren Seite sein...

VIelen Dank

[Uwe Raabe]

Versuch mal

Delphi-Quellcode:

Query.FetchOptions.Mode := afAll

vor dem Open.

[jobo]

Zitat:

Zitat von Ykcim (Beitrag 1489185)

Ich muss mich an diese Denkweise gewöhnen. Eigentlich packe ich immer so wenig wie möglich in die Gruppierung, weil ich mir einbilde, dass die Performance dann besser ist...

Auch wenn das am Beispiel schon geklärt ist:
Wenn Du Dir die mySQL Denkweise abgewöhnst, dann bist Du genau richtig. Diesen Mist gibt es nur bei mySQL und er funktioniert nicht zuverlässig.
Unabhängig von MSSQL, selbst in mySQL würde ich alle Group By Abfragen auf "normal" umstellen, falls Du da noch Projekte laufen hast. Und die Datenbank so umkonfigurieren, dass sie diese schlampige Notation nicht mehr akzeptiert. Dann verhält sich mySQL wie richtige Datenbanken und versucht nicht schlau zu sein und falsch zu liegen. Das Schlimme ist ja, dass Du es ab ein paar Tausend Datensätzen nur mit einigem Aufwand nachvollziehen kannst, ob richtig oder falsch.

Vergiß die alte Denkweise einfach, falls es schneller sein sollte- was kaum nachvollziehbar wäre-, lohnt sich das Risiko überhaupt nicht.

[Ykcim]

Zitat:

Versuch mal Query.FetchOptions.Mode := afAll vor dem Open.

Das war erfolgreich. Vielen Dank!

Zitat:

Wenn Du Dir die mySQL Denkweise abgewöhnst, dann bist Du genau richtig.

Harte Worte für jemanden, der seit 15 Jahren sich MySQL selbst angeeignet hat :?
Aber ich bin ja lernfähig :thumb:

Vielen Dank!

[jobo]

Zitat:

Zitat von Ykcim (Beitrag 1489423)

Zitat:

Wenn Du Dir die mySQL Denkweise abgewöhnst, dann bist Du genau richtig.

Harte Worte für jemanden, der seit 15 Jahren sich MySQL selbst angeeignet hat :?
Aber ich bin ja lernfähig :thumb:

:thumb:
Es ist wirklich ein gut gemeinter Hinweis. Schau Dir ein x-beliebiges mySQL Forum an (Ich glaube bei Maria ist es analog, da weiß ich nicht, wann dieses Verhalten- also ab welcher Version- per Default umgestellt wurde auf "normal" bzw. richtig.) Es wimmelt von Group By Fragen. Und das wäre ja noch okay, wenn es um das reine Verständnis geht.
Die Leute, die nicht fragen und die Fehler in ihrer Datenausgabe nicht bemerken, sind am Ende die Dummen. Es ist mir ein Rätsel, warum so ein Produkt existiert (und so verbreitet ist), das willkürlich falsche Daten ausgibt.

Und nur so:
Die Faustregel für eine korrekte Group By Syntax (die nebenbei schon immer auch bei mySQL funktioniert):
> alle Spalten der Ausgabe müssen entweder gruppiert oder aggregiert werden,
ist doch recht übersichtlich.

[Redeemer]

Ja, es ist etwas unlogisch, dass wenn du ein JOIN-Kriterium ein PK ist und trotzdem man alle Felder aggregieren oder gruppieren muss. Aber es ist konsequent bzgl. der Regel dass jedes Feld aggregiert oder gruppiert ist.

Übrigens:
- Bei MSSQL fasst man Bezeichner in eckigen Klammern ein oder notfalls in doppelten Anführungszeichen. Einfache Anführungszeichen sind für Strings.
- IIF ist übersichtlicher als ein CASE-WHEN-Konstrukt mit zwei Situationen.
- COALESCE mit zwei Parametern ist zwar dasselbe wie ISNULL, aber ISNULL ist leichter verständlich. /Edit: Und performanter.
- Und bitte benutze Views, statt so viele Zeilen SQL im Quelltext zu haben.
- Und zu QuotedStr kein Kommentar.
Na ja gut, egal. Namen der Tabellen und Felder sind der Horror. Dachte erst Paradox, aber das kannte immerhin längere Feldnamen.

Zitat:

Zitat von Delphi.Narium (Beitrag 1489255)

In "normalem" SQL wäre das wohl so erforderlich:

SQL-Code:

SELECT
[...]
         CASE WHEN b.in__vrrd < a.minstock then 'Mindestmenge unterschritten' ELSE '' END AS 'Kommentar',
[...]
GROUP BY 
[...]
  'Kommentar'  -- da weiß ich jetzt garnicht, ob MS-SQL das so kann

Nein, kann es nicht. Felder, die du nur aus Aggregaten oder Gruppen generierst, gruppierst du nicht. Sonst gruppierst du dasselbe Feld (bzw. hier zwei Felder) zweimal, das ergibt keinen Sinn.

[himitsu]

Egal ob AutoFetch/FetchAll/... oder nicht,
sollte doch dennoch RecordCount mit der Gesamtzahl von der DB übereinstimmen? (fast alle anderen DBComponentenHersteller bekommen das ja auch hin)

[jobo]

Zitat:

Zitat von Redeemer (Beitrag 1489479)

Ja, es ist etwas unlogisch, dass wenn du ein JOIN-Kriterium ein PK ist und trotzdem man alle Felder aggregieren oder gruppieren muss. Aber es ist konsequent bzgl. der Regel dass jedes Feld aggregiert oder gruppiert ist.

Die Fehlermeldung im ersten Post ist ziemlich exakt, wenn man ihr folgt und das bemeckerte Feld in die Group By Clause schreibt, wird die nächste kommen. Man folgt also der Fehlermeldung so lange, bis nicht mehr gemeckert wird und das DB System in der Lage ist, die Abfrage richtig zu "verstehen". Dann erhält man korrekte Daten. (analog kann man das Spielchen machen und die bemeckerten Felder aggregieren)

Es gibt DB Systeme, die anhand von Schlüsselfeldinformationen und sagen wir mal lazy geschriebenen Group By Clauses trotzdem richtig arbeiten. Das ist ein Feature, die Systeme haben das dokumentiert.

By mySQL ist es leider nicht so, zumindest nicht im 0815 frisch installiert Modus. Es glaubt nur, es könnte auch mit mangelhaften Group By Anweisungen eindeutig richtige Annahmen zur notwendigen Gruppierung treffen, es vertut sich aber manchmal. Und wann es das macht, ist nicht transparent. An der Stelle gibt es keine Fehlermeldung. Man bekommt einfach falsche Daten.

Es ist schon ein gehöriger Unterschied, ob man eine Fehlermeldung wegen einer falschen/(unsauber kennt das System nicht) bekommt oder falsche Daten wegen falscher Interpretation der Abfrage.

[Uwe Raabe]

Zitat:

Zitat von himitsu (Beitrag 1489481)

Egal ob AutoFetch/FetchAll/... oder nicht,
sollte doch dennoch RecordCount mit der Gesamtzahl von der DB übereinstimmen? (fast alle anderen DBComponentenHersteller bekommen das ja auch hin)

Muss man halt nur entsprechend einstellen: RecordCountMode

Delphi-Quellcode:

Query.FetchOptions.RecordCountMode := cmTotal;