Abfrage: Laufende Summe einer Spalte 1 in Abhängigkeit einer anderen Spalte 2 (je unique Wert Spalte 2)

Base ermöglicht es Ihnen, Ihre Daten in einer Datenbank direkt mit LibreOffice zu bearbeiten.
EinsamerBaumWald
Beiträge: 31
Registriert: Fr 28. Nov 2014, 20:41

Re: Abfrage: Laufende Summe einer Spalte 1 in Abhängigkeit einer anderen Spalte 2 (je unique Wert Spalte 2)

Beitrag von EinsamerBaumWald » So 11. Sep 2016, 17:13

Hi Rik,

vielen Dank!

Ich habe die die Casewhen-Bedingung folgendermaßen geändert:

Code: Alles auswählen

Casewhen( "lfdNr" =1 AND "Laufende Summe" > "Menge",1, Casewhen( "Laufende Summe" = "Menge", MAX( "lfdNr"), MAX( "lfdNr" ) + 1 )) AS "LetzterSack"
und bei WHERE die Bedingung

Code: Alles auswählen

"Laufende Summe" <= "Menge"
durch

Code: Alles auswählen

 ("Laufende Summe" <= "Menge"
		OR "lfdNr" = 1)
ersetzt.
Damit funktioniert es einwandfrei.

Der besseren Lesbarkeit wollte ich casewhen als CASE WHEN schreiben, aber bereits bei der Umformulierung Deines Codes bekomme ich die Fehlermeldung:
Fehler-Code: -106
Not a condition in statement

Code: Alles auswählen

CASE WHEN 
			"Laufende Summe" = "Menge" 
		THEN
			MAX( "lfdNr")
		ELSE
			 MAX( "lfdNr" ) + 1 
		END		  AS "LetzterSack"
Wo liegt der Fehler?

Vielen Dank,
EinsamerBaumWald

F3K Total
Beiträge: 1825
Registriert: So 10. Apr 2011, 10:10

Re: Abfrage: Laufende Summe einer Spalte 1 in Abhängigkeit einer anderen Spalte 2 (je unique Wert Spalte 2)

Beitrag von F3K Total » Mo 12. Sep 2016, 19:35

Sorry,
keine Ahnung, ich bekomme es selbst nicht besser hin. Ist ungewöhnlich, kann es sein, dass es an der Aggregatfunktion MAX() liegt?
Gruß R

EinsamerBaumWald
Beiträge: 31
Registriert: Fr 28. Nov 2014, 20:41

Re: Abfrage: Laufende Summe einer Spalte 1 in Abhängigkeit einer anderen Spalte 2 (je unique Wert Spalte 2)

Beitrag von EinsamerBaumWald » Fr 23. Sep 2016, 22:58

Hi,

vielen Dank für die Bestätigung. Ich hab mich schon gefragt, ob ich da einen dummen Syntaxfehler drin habe. Laut dem HyperSQL User Guide sollte es da keine Einschränkungen geben, weder bei den Aggregatfunktionen noch bei der CASE-Formulierung.
Auf https://bugs.documentfoundation.org/ habe ich nicht einen verwandten Bugreport zu CASE / CASEWHEN gefunden. Sollte ich das melden? Dazu müsste man allerdings erst den Code generalisieren und schauen, ab wann es fehlschlägt - dafür habe ich allerdings gerade keine Kapazitäten frei.

Anderes Thema:
Hat vielleicht doch noch jemand eine Idee, wie die Abfrage "LaufendeSummierung" editierbar zu bekommen ist?

Ich habe jetzt mal meine hier geschilderte Idee umgesetzt:
Der Inhalt der Spalte "Sackliste_für_Bestellung"."id" kopiere ich in die neuangelege Tabelle "Bestellung_Sackliste", die neben einem Primarschlüssel nur eine Spalte "Auswahl", Typ Checkbox enthält.

Das Formular "Sackliste_für_Bestellung" in dem Formular Bestellungen hat als Datengrundlage dann die Abfrage "Sackliste_für_Bestellung_kontrollieren":

Code: Alles auswählen

SELECT
    "Bestellung_Sackliste"."id" AS "ID",
    "Bestellung_Sackliste"."Auswahl",
    "Kräuter".*
FROM
    "Bestellung_Sackliste",
    "Kräuter"
WHERE
    "Bestellung_Sackliste"."Sack-id" = "Kräuter"."id"
Nun können Säcke abgewählt werden.
Jetzt habe ich leider die laufende Summierung nicht mehr in dieser Anzeige, deshalb habe ich die Abfrage "Bestellung_zu_bearbeiten" erweitert um eine Summierung der ausgewählten Säcke nach Krautart.

Nun hätte ich natürlich gerne, dass wenn ein Sack abgewählt wurde, der nächste Sack dazu genommen wird:

Code: Alles auswählen

WHERE
	CASE 
		WHEN "Bestellung_Sackliste"."Sack-id" = "a"."id" 
			THEN "Bestellung_Sackliste"."Auswahl" <> FALSE
		ELSE	
			TRUE	
		END
in LaufendeSummierung
Das gibt eine Fehlermeldung HAVING statt WHERE liefert keine Fehlermeldung mehr, aber funktionieren tut es auch nicht.
Im HSQL UserGuide fand ich auch folgenden Hinweis:
The result of a case expression is always a value
Dies erklärt, warum ich CASE nicht innerhalb von WHERE / HAVING einsetzen kann.

Nun habe ich versucht, dass mit einem LEFT JOIN zu lösen, was grundsätzlich auch funktioniert:

Code: Alles auswählen

SELECT
    COUNT(*) as "lfdNr",
    "A"."id",
	"A"."Lagerort",
    "A"."Kraut",
    "A"."Sacknummer",
	"A"."Chargennummer",
	"A"."Gewicht",  
    SUM( "B"."Gewicht" ) AS "Laufende Summe"
FROM
    "Kräuter" AS "A"
INNER JOIN 
    "Kräuter" AS "B"
	ON 
	    "B"."id" <= "A"."id"
	AND
    	"B"."Kraut" = "A"."Kraut"
	AND
	    "A"."verkauft" <> TRUE
	AND
    	"B"."verkauft" <> TRUE

LEFT JOIN "Bestellung_Sackliste" 
	ON "A"."id" = "Bestellung_Sackliste"."Sack-id" 
	WHERE
		 "Bestellung_Sackliste"."Auswahl" = TRUE
		OR
		 "Bestellung_Sackliste"."Auswahl" IS NULL

GROUP BY
    "A"."id",
	"A"."Lagerort",
    "A"."Kraut",
    "A"."Sacknummer",
	"A"."Chargennummer",
    "A"."Gewicht"
Abfrage: LaufendeSummierung_

Aber jetzt stimmt die Laufende Summe natürlich noch nicht. Wenn ich aber das LEFT JOIN vor das INNER JOIN stelle, bekomme ich die Fehlermeldung
Column not found: B.Gewicht
Ich bin gerade etwas ratlos - wer weiß Rat und kann helfen?


Vielen Dank und Grüße,
EinsamerBaumWald
Dateianhänge
Lagerbuch.odb.zip
(48.97 KiB) 55-mal heruntergeladen

F3K Total
Beiträge: 1825
Registriert: So 10. Apr 2011, 10:10

Re: Abfrage: Laufende Summe einer Spalte 1 in Abhängigkeit einer anderen Spalte 2 (je unique Wert Spalte 2)

Beitrag von F3K Total » Sa 24. Sep 2016, 11:58

Hi,
ich komme nicht mehr klar, steige nicht mehr durch. Darum habe ich dir anbei mal einen ganz anderen Ansatz gebaut, bei dem das Formular Bestellungen die meiste Arbeit übernimmt.
Es gibt eine Tabelle "Verkauft" in die die verkauften Säcke eingetragen werden. Die verfügbaren Säcke ergeben sich aus der Differenz zwischen "Kräuter" und "Verkauft". Es ist zwar mehr Handarbeit nötig, dafür hat man die volle Kontrolle und benötigt kein Makro.
Vorgehen:
  • Bestellung im Formular oben links wählen
  • Kraut im Tabellenkontrollfeld darunter -> alle Kräuter, die verfügbar sind, werden rechts oben angezeigt
  • einen Sack wählen, Haken bei "verkauft" setzen, aktualisieren drücken -> es wird ein Datensatz in der Tabelle "Verkauft" angelegt
  • Die aktuelle Gewichtssumme wird errechnet
  • Wenn zufrieden, fertig, wenn falscher Sack -> Rechtsklick auf den Zeilenkopf des unteren rechten Tabellenkontrollfeldes oder Button "löschen" -> aktualisieren, der Sack ist wieder verfügbar und es kann ein anderer gewählt werden.
Die Rechnungen können nun anhand der Tabelle "Verkauft" erstellt werden.

Gruß R
Dateianhänge
Lagerbuch.zip
(20.15 KiB) 56-mal heruntergeladen

EinsamerBaumWald
Beiträge: 31
Registriert: Fr 28. Nov 2014, 20:41

Re: Abfrage: Laufende Summe einer Spalte 1 in Abhängigkeit einer anderen Spalte 2 (je unique Wert Spalte 2)

Beitrag von EinsamerBaumWald » So 25. Sep 2016, 01:16

Hi,

vielen Dank für die Arbeit, die Du da investiert hast. Schade, dass Du Dich nicht mehr reindenken kannst. Deinen Ansatz kann ich nicht ganz übernehmen. Hauptgrund ist, dass ich nicht mehrere Säcke auf einmal zum Verkauf auswählen kann, und bei meist 30 bis 50 Säcken pro Bestellung wäre das etwas mühsam. Die vorgeschlagenen Säcke möchte ich in 95% der Fälle auch übernehmen, nur selten eben nicht.
(Die Kundendaten in eine eigene Tabelle auslagern habe ich noch vor.)


Auf jeden Fall habe ich das Problem mit der Abfrage / Ansicht LaufendeSummierung lösen können, nachdem ich mir Deine Abfragen angeschaut habe:

Code: Alles auswählen

SELECT
    COUNT(*) as "lfdNr",
    "A"."id",
	"A"."Lagerort",
    "A"."Kraut",
    "A"."Sacknummer",
	"A"."Chargennummer",
	"A"."Gewicht",  
    SUM( "B"."Gewicht" ) AS "Laufende Summe"
FROM
    "Kräuter" AS "A"
INNER JOIN 
    "Kräuter" AS "B"
	ON 
	    "B"."id" <= "A"."id"
	AND
    	"B"."Kraut" = "A"."Kraut"
	AND
	    "A"."verkauft" <> TRUE
	AND
    	"B"."verkauft" <> TRUE

	 WHERE 
		"B"."id" NOT IN 
				( SELECT 
					"Sack-id"  
					FROM "Bestellung_Sackliste" 
					WHERE
					 	"Bestellung_Sackliste"."Auswahl" = FALSE
				)
GROUP BY
    "A"."id",
	"A"."Lagerort",
    "A"."Kraut",
    "A"."Sacknummer",
	"A"."Chargennummer",
    "A"."Gewicht"
Ich habe außerdem den Makrocode erweitert und einen Button eingefügt, um Säcke für eine Bestellung vorschlagen zu lassen.
In dem angehängten Beispiel habe ich den Sack P 52 abgewählt. Wird jetzt nochmal der Button gedrückt, dann wird ein anderer Sack vorgeschlagen. Ich kann aber auch stattdessen einen Sack manuell hinzufügen. (Um das grafisch zu lösen, muss ich noch was bauen.)



Vielen Dank & Gruß,
EinsamerBaumWald
Dateianhänge
Lagerbuch.odb.zip
(37.41 KiB) 57-mal heruntergeladen

F3K Total
Beiträge: 1825
Registriert: So 10. Apr 2011, 10:10

Re: Abfrage: Laufende Summe einer Spalte 1 in Abhängigkeit einer anderen Spalte 2 (je unique Wert Spalte 2)

Beitrag von F3K Total » So 25. Sep 2016, 01:47

Hallo EinsamerBaumWald,
das ist schade, ich halte die Methode mit Eingangs- und Ausgangstabelle für sehr gut. Mit einem Makro wäre es hier kein Problem, die Säcke erstmal bis zur gewünschten Menge aufzufüllen, und dann von Hand nachzulegen, nur als Denkansatz. Vermutlich bist du schon zu weit fortgeschritten um noch mal von vorn zu beginnen.
Es hat dir ja wenigstens dass "... NOT IN (SELECT...)" gebracht. Du verwendest jetzt also die gleiche Technik, Differenzbildung ;)
Gruß R

EinsamerBaumWald
Beiträge: 31
Registriert: Fr 28. Nov 2014, 20:41

Nach Update auf HSQL 2.3: Probleme mit Group by und casewhen in Kombination mit Aggregatfunktion MAX()

Beitrag von EinsamerBaumWald » Mi 22. Mai 2019, 11:54

Hallo F3K Total,

ich war zwar länger nicht hier aktiv, habe aber die ganze Zeit an meiner Datenbank gearbeitet. Nochmal vielen Dank für Deine Hilfe!
Schon länger habe ich eine Split-Database daraus gemacht, und wollte jetzt (anlässlich eines Neuaufsetzens nach einer korrupten Datenbank - Stromausfall?) auf HSQL 2.3.2 aktualisieren - von 1.8.0.
Dabei schaffe ich es aber nicht, die Ansicht "Sackliste_für_Bestellung" zum Laufen zu bringen. So funktionierte es unter HSQL 1.8:

Code: Alles auswählen

SELECT
    "id",
    "Lagerort",
    "Kraut",
    "Sacknummer",
    "Chargennummer",
    "Gewicht",
    "Laufende Summe",
    "Kunde",
    "Lieferdatum"
FROM
    "LaufendeSummierung",
    "Bestellungen",
	(
		SELECT
		"Produkt",
		CASEWHEN( "lfdNr" =1 AND "Laufende Summe" > "Menge",1, CASEWHEN( "Laufende Summe" = "Menge", MAX( "lfdNr"), MAX( "lfdNr" ) + 1 )) AS "LetzterSack",
		"Kunde",
		"Lieferdatum"
	FROM
	    "LaufendeSummierung",
	    "Bestellung", 
	    "Bestellungen"
	
	WHERE
	    "Kraut" = "Produkt"
	AND
	    ("Laufende Summe" <= "Menge"
		OR 
		"lfdNr" = 1)
	AND 
    	"gebucht" <> TRUE
	AND 
	    "bearbeiten" = TRUE
	AND
		"Bestellung"."Bestell_ID" = "Bestellungen"."id"
	GROUP BY
	    "Produkt",
	    "Kunde",
	    "Lieferdatum"
	)
	 AS  "LetzterSack"
WHERE
    "Kraut" = "Produkt"
AND
    "lfdNr" <= "LetzterSack"
AND 
   	"gebucht" <> TRUE
AND 
    "bearbeiten" = TRUE;
Unter HSQL 2.3 gibt es folgende Fehlermeldung:
expression not in aggregate or GROUP BY columns: CASEWHEN(((PUBLIC."LaufendeSummierung"."lfdNr"=1) AND (PUBLIC."LaufendeSummierung"."Laufende Summe">PUBLIC."Bestellung"."Menge")),1, CASEWHEN((PUBLIC."LaufendeSummierung"."Laufende Summe"=PUBLIC."Bestellung"."Menge"),( MAX(PUBLIC."LaufendeSummierung"."lfdNr")),(( MAX(PUBLIC."LaufendeSummierung"."lfdNr"))+1)))
Wenn ich den Ausdruck zu Group by hinzufüge, gibt es die Fehlermeldung:
invalid GROUP BY expression: MAX(PUBLIC."LaufendeSummierung"."lfdNr")
Im Forum hier fand ich eine mögliche Erklärung, aber ich weiß nicht, wie ich die Aggregatfunktion MAX() nicht innerhalb von casewhen formulieren sollte.
(Die Schreibweise mit "case when" funktioniert jetzt, bringt aber keinerlei Unterschied bei den Fehlermeldungen.)

Ich wäre für einen Tipp sehr dankbar, da ich nichts passendes gefunden habe, obwohl ich jetzt schon mehrere Stunden gesucht & gelesen habe. Besten Dank!

F3K Total
Beiträge: 1825
Registriert: So 10. Apr 2011, 10:10

Re: Abfrage: Laufende Summe einer Spalte 1 in Abhängigkeit einer anderen Spalte 2 (je unique Wert Spalte 2)

Beitrag von F3K Total » Mi 22. Mai 2019, 19:10

Probiere mal folgendes, erstelle eine Ansicht MAX_LFDNR wie folgt:

Code: Alles auswählen

SELECT MAX("lfdNr")"MAXLFDNR","Kraut" FROM "LaufendeSummierung" GROUP BY "Kraut"
und ersetze dann deine Ansicht "LaufendeSummierung" wie folgt:

Code: Alles auswählen

SELECT
    "id",
    "Lagerort",
    "Kraut",
    "Sacknummer",
    "Chargennummer",
    "Gewicht",
    "Laufende Summe",
    "Kunde",
    "Lieferdatum"
FROM
    "LaufendeSummierung",
    "Bestellungen",
	(
	SELECT
		"Produkt", CASEWHEN( "lfdNr" =1 AND "Laufende Summe" > "Menge",1, CASEWHEN( "Laufende Summe" = "Menge","MAXLFDNR", "MAXLFDNR" + 1 )) AS "LetzterSack",
		"Kunde",
		"Lieferdatum"
	FROM
	    "LaufendeSummierung",
	    "Bestellung", 
	    "Bestellungen",
        "MAX_LFDNR"
	
	WHERE
	    "LaufendeSummierung"."Kraut" = "Bestellung"."Produkt"
	AND
        "MAX_LFDNR"."Kraut" = "LaufendeSummierung"."Kraut"
    AND 
	    ("Laufende Summe" <= "Menge"
		OR 
		"lfdNr" = 1)
	AND 
    	"gebucht" <> TRUE
	AND 
	    "bearbeiten" = TRUE
	AND
		"Bestellung"."Bestell_ID" = "Bestellungen"."id"
	)
	 AS  "LetzterSack"
WHERE
    "Kraut" = "Produkt"
AND
    "lfdNr" <= "LetzterSack"
AND 
   	"gebucht" <> TRUE
AND 
    "bearbeiten" = TRUE
Gruß R

Antworten