View Full Version : SQL Listagg systemfehler
Gibt's dazu ein kleines Beispiel?
Ich habe eine Kopf Datei
und detail Daten
und Stammdaten
zu einer Kopf information gehören x Detail Sätze
Diese können unterschiedliche PE (PostEingang) Daten haben.
Die stehen als tt mm jjjj in 3 verschiedenen Feldern in einem Satz
Die brauche ich alle hintereinander zu den Kopf und Stamm Daten
Die MIN funktion brauche ich weil das ganze gegroupt ist, auf Alle ausgabe Felder
Sonst lässt er das Listagg nicht zu.
Es hat nichts mit dem echten Min( ) zu tun.
das listagg kann ich auch in den group packen, selbe ergebniss
Hier ist ein Beispiel, das genau das machen sollte, was Du brauchst:
Auftrags-Positions-Datei und Artikel-Stamm werden verknüpft. Kopf-Daten kannst Du ja noch dazujoinen.
Auftrags-Positions-Datei wird auf Auftrag verdichtet und alle Artikel-Nr. incl. Artikel-Bezeichnung und Lieferwert werden hintereinander aufgelistet.
Nach wie vor ist dafür weder ein MIN noch ein LATERAL noch ein verschachtelter Sub-Select erforderlich.
Select d.Company, d.OrderNo,
ListAgg(Cast('Item No: ' as VarChar(10) CCSID 1208) concat
Trim(d.ItemNo) concat ' ' concat RPAD(Descript, 20) concat
' Del.Val.: ' concat LPAD(DelQty * Price, 8) concat ' €',
' , ') ListItems
From OrderDetX d join ItemMastx i on d.Company = i.Company
and d.ItemNo = i.ItemNo
Group By d.Company, OrderNo
Order By Company, OrderNo
;
Welche Verknüpfung vermisst du denn?
select f1, f2, liste
from header h
cross join lateral (
select list_agg(...) as liste
from details d
where h.f1 = d.f1 and h.f2 = d.f2
)
Der Trick ist doch, dass die Where-Klausel die Join-Beziehung darstellt, ausschließlich Zeilen dieser Whereklausel berücksichtigt und das Aggregat sich genau auf diese Zeilen bezieht.
Wenn du allerdings zwischen Heder und Detail die Beziehung gar nicht hast, habe ich deine Aufgabe nicht verstanden:
"zu einer Kopf information header gehören x Detail Sätze
Diese können unterschiedliche PE (PostEingang) Daten haben.
Die stehen als tt mm jjjj in 3 verschiedenen Feldern in einem Satz
Die brauche ich alle hintereinander zu den Kopf und Stamm Daten"
Noch ein Hinweis: Ein Group By ist nur erforderlich, wenn ich Gruppensummen benötige. Frage ich ausschließlich Aggregate ab und spezifiziere die Where-Klausel entfällt der Group By da ich keine zusätzliche Selektionsfelder benötige. Diese stecken ja bereits in der Where-Beziehung.
Der Lateral hat noch den Seiteneffekt der besseren Optimierung über Index da Lateral nur für diesen Zugriff explizit optimiert wird.
Der Witz deines 1. skalaren Subselects ist doch, dass der Group By da vollkommen unnötig ist. Wenn die anderen 3 Dateien mit "Datei" nichts zu tun haben und du tatsächlich eine Gruppierung nicht benötigst, sind beide Group By nicht erforderlich und der Min() muss sogar entfallen:
<code><code>Select a1, b1, c1,
min((select listagg(digits(tt) concat '.' concat
digits(mm) concat '.' concat
digits(jj) , ', ')
from Datei
where f1=a1 and f2=a2 group by f1, f2))
from ... (3 Dateien)
where ...(einfache Feld=Feld verknüpfung und Feld = Wert Satzauswahl)
group by (alle Felder des select
order by .... </code></code>
Übrigens lässt sich mit dem "join lateral" vieles erledigen, wozu man früher viele ineffektive skalare Subselects benötigte.
Vielleicht ohne min((select vor listagg() und mit einem großen from und einem großen where und einem großen group by?
@Baldur:
1. Warum sollte man einen CROSS JOIN in Verbindung mit LATERAL wollen, wenn er nicht erforderlich ist?
2. Warum sollte man Tabellen/Views/UDTFs in den WHERE-Bedingungen anstatt in den FROM-Anweisungen verknüpfen wollen?
Schau Dir mal die folgenden SQL-Statements an. Ich habe Deine Syntax in mein Beispiel übersetzt. Welches ist wohl einfacher zu verstehen?
Lösung Baldur:
Select Company, OrderNo, DelDate, ListItems
from OrderHdrx h cross join
Lateral(Select ListAgg(Cast('Item No: ' as VarChar(10) CCSID 1208) concat
Trim(d.ItemNo) concat ' ' concat RPAD(Descript, 20) concat
' Del.Val.: ' concat LPAD(DelQty * Price, 8) concat ' €',
' , ') ListItems
from OrderDetx d join ItemMastx i on d.Company = i.Company
and d.ItemNo = i.ItemNo
Where h.Company = d.Company and h.OrderNo = d.OrderNo);
Lösung Birgitta:
Select h.Company, h.OrderNo, DelDate,
ListAgg(Cast('Item No: ' as VarChar(10) CCSID 1208) concat
Trim(d.ItemNo) concat ' ' concat RPAD(Descript, 20) concat
' Del.Val.: ' concat LPAD(DelQty * Price, 8) concat ' €',
' , ') ListItems
From OrderHdrx h join OrderDetx d on h.Company = d.Company
and h.OrderNo = d.OrderNo
join ItemMastx i on d.Company = i.Company
and d.ItemNo = i.ItemNo
Group By h.Company, h.OrderNo, DelDate
Order By Company, OrderNo;
M.E. am einfachsten zu Verstehen und auch am einfachsten zu Testen ist die folgende Syntax (liefert ebenfalls das gleiche Ergebnis)
With x as (Select d.Company, d.OrderNo,
ListAgg(Cast('Item No: ' as VarChar(10) CCSID 1208) concat
Trim(d.ItemNo) concat ' ' concat RPAD(Descript, 20) concat
' Del.Val.: ' concat LPAD(DelQty * Price, 8) concat ' €',
' , ') ListItems
From OrderDetx d join ItemMastX i on d.Company = i.Company
and d.ItemNo = i.ItemNo
Group By d.Company, d.OrderNo)
Select h.Company, h.OrderNo, DelDate, ListItems
from OrderHdrx h join x on x.Company = h.Company and x.OrderNo = h.OrderNo;
Ganz einfach, weil der Lateral so sehr gut funktioniert.
Du kannst natürlich auch einen
left/inner join lateral (....) x on 1=1
durchführen, was aufs selbe Ergebnis kommt, man spart sich nur den "on 1=1".
Da Cross wie inner wirkt, kann man auf left ausweichen, wenn kein Ergebnis (NULL) möglich ist.
Warum where?
Weil der Where direkt über den Index geht und für den Lateral die Daten genau auf diesen Filter einschränkt und somit Group By überflüssig macht.
Was du da machst ist einfach zu viel des Guten, weil du
a) eine unnötige Gruppierung erzwingst und
b) eine unnötige Beziehung (= where) durchführen musst, was
c) den Optimizer schon mal vor Probleme stellen kann.
Warum?
Weil der "Join Lateral" individuell je Zeile ausgeführt wird, so wie man programmatisch denkt, somit per Where des Lateral je Zeile des Headers angewendet wird und das Ergebnis erheblich schneller zur Verfügung steht.
Und wer kennt nicht die Probleme, dass ein Order By in derived Tables entweder nicht erlaubt ist oder ignoriert wird?
Du kannst sehr locker
left join lateral (select ....
order by datum desc
imit 1
) x on 1=1
genau 1 Satz nach Spalte auf/absteigend sortiert auslesen.
Schau dir hier das 2. Beispiel an:
https://www.ibm.com/docs/en/informix-servers/12.10.0?topic=clause-lateral-derived-tables
Und hier auch für RPG-Fans.
https://www.rpgpgm.com/2021/10/using-lateral-in-sql.html
Bei mir hat sich der Lateral inzwischen für viele Dinge etabliert, ins besonders die Zusammenfassung mehrerer scalarer Subselects oder eben auch mehrerer Aggregate auf dieselbe Tabelle zu einem einzigen Lateral.
Und was das Verständnis angeht, so ist mein SQL einfacher als dein komplexer verjointer SQL.
Von der Performance ganz zu schweigen.
Da ein CTE nur eine einfachere Schreibweise eines Derived Tables ist und somit keine temporäre Tabelle erstellt, wird erst dein Ergebnis des Group By mit der On-Beziehung verknüpft und nicht die Basisdaten selber.
Nachtrag:
Entgegen deinem Hinweis auf Lateral in Verbindung mit Table Functions ist mir das gar nicht begegnet, sondern nur beim Nachsehen von Neuerungen bei SQL's der IBM i.
Also, ...
ich kann aus dem Kundessystem nicht in Internet.
Und raus kopieren von Text in mein system geht auch nicht.
Daher kann ich das komplette Statement nicht posten es sein den, ich schreib den Roman ab.
Da in der Selektion auch andere Sum(), min() und AVG() Funktionen drin sind, brauche ich den unteren Group by.
Sowas denkt man sich doch nicht aus ....
Habe es per Pgm gelöst.
Danke Euch
Da du den kompletten SQL ja nicht gepostet hast, kann ich dir da auch nur rudimentär helfen.
Trotzdem kann man per Lateral-Table ebenso mehrere Aggregate sowie auch mehrere Laterals vewenden:
select f1, f2
, dl.*
, ag.*
, d2.*
from Headers h
left join lateral (select list_agg(....) as h_listagg
from detail d
where h.f1 = d.f1 and h.f2 = d.f2
) dl on 1=1
left join lateral (select min(xxx) as ag_min
, max(xxx) as ag_max
, count(*) as ag_count
, sum(yyy) as ag_sum
.....
from header ag
where h.f1 = ag.f1 and h.f2 = ag.f2
) ag on 1=1
left join lateral (select min(d2.xxx) as d2_min
, max(d2.xxx) as d2_max
, count(*) as d2_count
, sum(d2.yyy) as d2_sum
.....
from header ag
inner join detail2 d2 on ag.f1 = d2.f1 and ag.f2 = d2.f2
where h.f1 = d2.f1 and h.f2 = d2.f2
) d2 on 1=1
somit sparst du dir den group by drumrum, da jeder lateral dir die Aggregate separat gefiltert auf den Gruppen-Schüssel liefern kann.
M.a.W: der Lateral kann einen Group By auf dem Haupt-Select komplett ersetzen.
Man muss sich nur mal mit den Möglichkeiten auseinander setzen.
... das ist der Stoff, aus dem Altlasten gedrechselt werden. SQL Statements sind ab einer gewissen Komplexität nicht mehr verifizierbar und wenn man dabei zu "fast" richtigen, scheinplausiblen Resultaten kommt, geht es ab in den finstersten Wald - zuweilen verirrt sich sogar die SQL Engine bereits in selbigen.
Das Ganze riecht für mich ohnehin nach schwersten Mängeln im Datenbank Design, die man angehen sollte.
D*B
Das Schöne ist tatsächlich, dass ein Lateral Table SQL's sogar stark vereinfachen kann.
Dann noch in Kombination mit den Window-Funktionen "xxx(...) over(...)" spart man sich sehr viele Zeilen Code. Und vor allem: es ist sehr schnell in der Ausführung.