SQL Abfrage mit while

[ADJUD]

Hallo,

ich verzweifele gerade an einer Abfrage. Ich habe in einer Tabelle (DLN1) 2 Felder die ich beide ausgeben möchte. In Echt sind es mehrere Spalten und Tabellen die mit joins verbunden werden - aber der Einfachheithalber habe ich es auf die relevanten Felder & Variablen beschränkt.

DocNum Quantity
13077970 1
13077971 12
13077980 1
13077981 1
13077982 1
13077983 1
13077984 1
13077985 1
13077986 1
13077987 1
13077988 3
13077989 2
13077990 1

Jetzt möchte ich eine Abfrage generieren, der mir beginnend mit der kleinsten DocNum die Mengen (Quantity) zusammenzählt und nur die Datensätze ausgibt die in Summe (@i) einen bestimmten Wert (@menge) nicht überschreiten.

Ich habe es mit folgendem Script gestestet aber kläglich gescheitert


DECLARE
@i int,
@menge int


set @menge = 20
set @i = 0

WHILE (@i < @menge)
BEGIN
select DocNum, Quantity from DLN1
set @i = @i + Quantity
END

Ausgabe sollte bei obigen Parametern/Variablen folgende sein:

DocNum Quantity
13077970 1
13077971 12
13077980 1
13077981 1
13077982 1
13077983 1
13077984 1
13077985 1


Irgenwie bekomm ich das nicht hin.

Geht das überhaupt mit while oder benötige ich einen cursor und wenn ja, wie würde der aussehen.

Danke vorab

[Ralf Jansen]

[HIGHLIGHT=SQL]SELECT DocNum, Quantity
FROM (SELECT DocNum, Quantity, SUM(Quantity) OVER (ORDER BY DocNum) as runningTotal
FROM DLN1
WHERE DocNum >= 13077970) A --StartWert
WHERE A.runningTotal < 20[/HIGHLIGHT]

[defo]

Falls Dich das Thema grundsätzlich interessiert.
Es nennt sich Windows Funktionen (alternativ zu dem Begriff Analytic Functions bei Oracle, die das irgendwann mal eingeführt haben)
Windows nicht wegen Windows OS von Microsoft, sondern weil mit diesen Funktionen bestimmte Datenfenster betrachtet werden.

Als einfache Idee, wie man das einsetzen kann, wäre Excel bzw. allgemein Spreadsheet Funktionalität zu nennen.
Datenzugriff, relativ zum "aktuellen" Datensatz, innerhalb einer vorgegebenen Sortierung und/oder Gruppe.

[ADJUD]

Hallo Ralf,

vielen Dank für die schnelle Antwort. Ich dachte ich bekomme eine Email wenn jemand darauf antwortet, habe deshalb das ganze Wochenende nicht auf das Forum geschaut. Leider habe ich aber keine Mail erhalten und deshalb auch meine verspätete Antwort.

Zu Deiner Lösung:
Ich bin mir nicht sicher - aber ich kenne zum Zeitpunkt der Abfrage den Startwert nicht.

Meine Abfrage Lautet eigentlich (ergibt alle offenen Belege eines Bestimmten Kunden mit einem bestimmten Artikel:

select ODLN.DocNum, DLN1.Quantity as Quantity
from DLN1
join ODLN on DLN1.DocEntry = ODLN.DocEntry
where ODLN.DocDate > '2016-02-01 00:00:00.000' and ODLN.CardCode = 11048 and DLN1.LineStatus != 'C' and DLN1.ItemCode = 'EP'
order by ODLN.DocNum

und aus diesem Resultat sollen sollen quasi beginnend mit der kleinsten DocNum nur die Angezeigt werden, die in Summe (Quantity) unter 20 ergeben.

Wie kann ich das mit Deiner Abfrage machen?

Danke vorab für Deine Mühe!

Tayfun

[ADJUD]

Hi defo,

keine Ahnung inwieweit mir das in meinem Fall hilft - weil ich das ja eher mit sql machen möchte. Ich bin kein Programmierer - nur ein kleiner Admin der sich auch etwas mit SQL beschäftigt (wenn nötig). Aber im obigen Fall komm ich mit meiner logik nicht weiter.

Gruß

Tayfun

[ADJUD]

Hallo Ralf,

ich habe das mal versucht umzuschreiben und bekomme aber eine Fehlermeldung:

SELECT DocNum, Quantity from (
select ODLN.DocNum as DocNum, DLN1.Quantity as Quantity, SUM(DLN1.Quantity) OVER (ORDER BY ODLN.DocNum) AS runningTotal
from DLN1
join ODLN on DLN1.DocEntry = ODLN.DocEntry
where ODLN.DocDate > '2016-02-01 00:00:00.000' and ODLN.CardCode = 11048 and DLN1.LineStatus != 'C' and DLN1.ItemCode = 'EP') A
WHERE A.runningTotal < 20

Fehler:
Meldung 102, Ebene 15, Status 1, Zeile 2
Incorrect syntax near 'order'.

Ich habe übrigens SQLServer 2008R2 Standard Edit

Wo ist der Fehler? Im Query Analyzer zeigt es vor Ausführung keinen Fehler an

[Christian Marquardt]

Die Forensoftware schickt zurzeit keine eMail. Aus welchem Grund das so ist, ist nicht bekannt

[defo]

keine Ahnung inwieweit mir das in meinem Fall hilft - weil ich das ja eher mit sql machen möchte. Ich bin kein Programmierer

Das hilft Dir nur weiter, wenn Du nach den Begriffen "sql analytic functions" oder "sql window functions" im Internet suchst. Das sind die Bezeichnungen, die für das Verfahren, das Ralph Dir gezeigt hat. Also einfach Namen, unter denen Du Beispiele und Erklärungen findest.

Excel war nur als Verständnishilfe gedacht, was da eigentlich passiert.

Mit "programmieren" hat das nicht mehr zu tun, als das Verfassen eines beliebigen anderen SQL Statement, diese "Window functions" sind eine SQL Erweiterung.

[ADJUD]

Danke defo, ich werde mich mal damit beschäftigen.

Mein Statment möchte ich eigentlich in Crystal Reports einbauen und über SAP B1 den Report mit Parameter aufrufen. Ich könnte natürlich auch in Crystal Reports Summieren und Filtern, würde aber unnötig viele Daten abrufen. Deshalb würde ich gerne vorher schon die Datenmenge begrenzen - denke das ist performanter.

[defo]

Das ist schon in Ordnung so, je weniger Daten an die Report Engine gehen, desto besser.

Versuch mal in Deinem fehlerhaften Statement, mehr Übersicht zu schaffen und lasse das äußere Select weg und probiere es damit. Ich kann so keinen Fehler erkennen.
Als Variante kannst Du auch mal das Statement so umbauen, dass DLN gar nicht verwendet wird. Es würde also das Quantity fehlen und ein paar Where Kriterien. Mir ist nicht bekannt, ob die DB Version das Over order by auf dem gejointen Feld beherrscht. Das ist aber nur geraten.

[Ralf Jansen]

Wenn ich die Doku richtig interpretiere geht dieser Syntax der OVER Clause (Aggregate Function+Order By) erst ab SQL Server 2012.
Für die 2008er Version muß man sich das wohl nachbasteln zum Beispiel mit einem self join. Das bekomm ich gerade aber nicht hin habe keine SQL Server zur Hand und sqlfiddle.com streikt bei mir gerade mal wieder (mag wohl irgendwas aus der Ecke von AdBlock, NoScript, Disconnect nicht mehr)

[ADJUD]

Hmm, jetzt habe ich 1x nur das innere ausgeführt >> gleicher Fehler.

Habe dann das Statement wie folgt umgebaut und die DocNum (Belegnummer) aus der Abfrage genommen und nur über die DocEntry (Interne Beleg-ID - PK/FK >> 1:n von ODLN zu DLN1) versucht zu summieren. Leider kommt immernoch die Fehlermeldung mit order by.

select DLN1.Quantity as Quantity, SUM(DLN1.Quantity) OVER (ORDER BY DLN1.DocEntry) AS runningTotal
from DLN1
where DLN1.LineStatus != 'C' and DLN1.ItemCode = 'EP'

Es muss also an dem SUM OVER liegen.

Gibt es noch eine alternative Lösung?

[defo]

Also hier steht, aber 2008
https://msdn.microsoft.com/de-de/library/ms189461.aspx

Oder sehe ich da was falsch?
Ich habe auch im Kopf, dass einfache Window Functions eben ab 2008 implementiert sind.
Ich setzte keine MS Server ein, daher kann ich nichts nachvollziehen.

Versuch das Statement so weit es geht abzuspecken, wie eben möglich.

[ADJUD]

MSDN.jpg

Laut MSDN sollte das schon ab 2008 funktionieren. Ich bekomme auch keine Fehlermeldung das der Befehl OVER nicht bekannt ist - was normalerweise kommt wenn man Befehle einfügt die nicht unterstützt werden

[ADJUD]

Hi defo, noch weiter abspecken als oben dargestellt kann ich es nicht mehr - sonst habe ich nichts mehr ;-)

ich habe das auch mal ohne where laufen lassen - same same :-(