Bericht 2spaltig

Datenbanklösungen mit AOO/LO

Moderator: Moderatoren

RobertG
*******
Beiträge: 1845
Registriert: Fr, 13.04.2012 19:28
Kontaktdaten:

Re: Bericht 2spaltig

Beitrag von RobertG »

hallo Frewer,

so, zur Entspannung noch einmal ein PAL gelöst (PAL - Problem anderer Leute; Per Anhalter durch die Galaxis ...)

Code: Alles auswählen

SELECT "T1".*, "T2".*, "T3".* FROM
(SELECT DISTINCT CASE WHEN (SELECT COUNT( "ID" ) FROM "DBTest" WHERE "Wer" = 'A'  AND "dbok" = 0)  > (SELECT COUNT( "ID" ) FROM "DBTest" WHERE "Wer" = 'E'  AND "dbok" = 0) THEN  (SELECT COUNT( "ID" ) FROM "DBTest" WHERE "Wer" = 'A'  AND "dbok" = 0 AND "ID" <= "a"."ID") ELSE (SELECT COUNT( "ID" ) FROM "DBTest" WHERE "Wer" = 'E'  AND "dbok" = 0 AND "ID" <= "a"."ID") END AS "Counter" FROM "DBTest" AS "a" WHERE "dbok" = 0) AS "T1" LEFT JOIN
(SELECT (SELECT COUNT("ID") FROM "DBTest" WHERE "Wer" = 'A' AND "dbok" = 0 AND "ID" <= "a"."ID") AS "lfdNr.", "a".* FROM "DBTest" AS "a" WHERE "a"."Wer" = 'A' AND "a"."dbok" = 0) AS "T2" ON "T1"."Counter" = "T2"."lfdNr." LEFT JOIN
(SELECT (SELECT COUNT("ID") FROM "DBTest" WHERE "Wer" = 'E' AND "dbok" = 0 AND "ID" <= "a"."ID") AS "lfdNr.", "a".* FROM "DBTest" AS "a" WHERE "a"."Wer" = 'E' AND "a"."dbok" = 0) AS "T3" ON "T1"."Counter" = "T3"."lfdNr." 
Kurz nur erklärt: Ich mache zuerst einmal eine Auflistung der Spaltennummerierung, die mehr Einträge hat. Dies ist die Tabelle "T1" mit der CASE WHEN Abfrage.
Danach frage ich nach dem ersten Kriterium und nach dem zweiten Kriterium ab. Die Nummerierung des Counters stellt zwangsläufig gleichzeitig die Maximalnummerierung dar. So kann kein Datensatz verloren gehen.

Gruß

Robert

Frewer
**
Beiträge: 34
Registriert: Do, 05.03.2020 16:10

Re: Bericht 2spaltig

Beitrag von Frewer »

Hallo Robert, ich möchte absolut nicht, dass Du Dir meinetwegen die Corona-Nächte um die Ohren haust. Dein oo-knowledge ist halt verlockend gut, so dass man davon lernen kann.
Für Deine Arbeit also besten Dank!!
Habe es ausprobiert und es tut perfekt was ich wünschte. Allerdings mit einem Schönheitsfehler - ich kann die Ansicht wegen folgender Fehlermeldung nicht abspeichern:
--SQL Original in Ansicht kopiert, abspeichern -> Fehlermeldung: "Column already exists: lfdnr. in statement [ ganze SQL ...]" SQL Status S0021 FCode: -27
--SQL Original geändert: in T2 statt lfdnr. lfdnr1. ersetzt. Ergebnis der Ausführung ok, abspeichern -> Fehlermeldung: "column aready exists: ID in statement [ganze SQL...]" SQL Status S0021 FCode -27
Da habe ich dann doch nichts verändert, weil m.E. die ID im Rahmen der COUNT() Funktion ja in allen Tabellem T1,T2,T3 stets die gleiche sein muss.

Dieser Fehler bleibt auch bei LibreOffice bestehen, das ich mir jetzt auf meinen Laptop heruntergeladen habe. Gleiche Meldung. Ebenfalls ein Abspeichern nicht möglich. Das Ausführen geht perfekt.

Falls Du doch noch Lust hast, Dich noch mal für das PFW (Problem FreWer) zu begeistern wäre ich Dich sehr dankbar. Meine Fortschritte gehen halt nur sehr langsam bei den vielen Möglichkeiten. Manchmal raucht schon der kopf beim Lesen der Dokumentationen, weil es schlicht eine mächtige Sprache ist. Da ist C dagegen ein Klacks.

Gruß
Frewer

RobertG
*******
Beiträge: 1845
Registriert: Fr, 13.04.2012 19:28
Kontaktdaten:

Re: Bericht 2spaltig

Beitrag von RobertG »

Hallo Frewer,

habe zur Zeit wieder Luft. Die SchülerInnen sind eben SaisonarbeiterInnen - da war aus unerfindlichen Gründen gestern der Teufel los.

Die Abfrage kannst Du nicht als Ansicht speichern, weil da gleichlautende Felder drin stehen. Darauf weist auch die Fehlermeldung zum Feld "ID" hin. Du musst zumindest "T2".* oder "T3".* so auflösen, dass da nur die Felder angezeigt werden, die Du benötigst und dass die Felder einen Alias haben. Dann hast Du überall unterschiedliche Bezeichner und die interne Datenbank bekommt das geregelt.

Also:

Code: Alles auswählen

... "T2"."Wer" AS "Wer2", "T2"."Kosten" AS "Kosten2" ...
dann klappt das hoffentlich.

Gruß

Robert

Frewer
**
Beiträge: 34
Registriert: Do, 05.03.2020 16:10

Re: Bericht 2spaltig

Beitrag von Frewer »

Hallo Robert,

ach die Lehrer, ich habe auch 2 in der Familie, allerdings beide wie ich in Pension!

Die Änderung klappt. Ich hatte ja bereits gesagt, dass ich rum experimentiert und mit lfdnr bereits eine Lösung gefunden hatte. Die ID hat mich aber beschäftigt, dafür hatte ich keine Lösung. Jetzt ist das alles klar und ich gehe an den Bericht.

Bin gerade dabei, im Handbuch das Beispiel "Sport" nach zu arbeiten, um weitere Kenntnisse zu erhalten. Da habe ich auch das CASE WHEN gefunden genauso wie die Erklärung des "*" (alle Felder) und damit auch Deine SQL "aufgedröselt" und verstanden.

Wenn ich wieder am Verzweifeln bin, werde ich mich wieder melden.

Wie gesagt, vielen Dank für die wichtige Unterstützung.
Gruß
Frewer

Frewer
**
Beiträge: 34
Registriert: Do, 05.03.2020 16:10

Re: Bericht 2spaltig

Beitrag von Frewer »

Hallo Robert,

habe noch Fragen zum SQL Code. Du beginnst mit <SELECT "T1"."Counter" AS "ctr","T2"."Wer" AS "Wer2", "T2"."Kosten" AS "Kosten2", "T3"."Wer" AS "Wer1", "T3"."Kosten" AS "Kosten1" FROM ... >. Das klappt auch alles jetzt prima.
Nun meine Fragen:
SELECT T1,T2,T3 stehen für was?
Das Ganze scheint eine Tabelle zu sein mit den Feldern T1.Counter, T2.xx, T2.yy, T3.xxx, T3.yyy. Dargestellt wird aber nur die jeweilige Spalte Counter, xx, yy, xxx, yyy. Und das <LEFT JOIN> heißt dann, dass die nächsten Spalten rechts davon stehen? D.h. ich könnte die Spalten weiteren <LEFT JOINT> weiter fortsetzen.
Ist das so ähnlich wie mit den Subformularen im Formular, denen man jeweils andere Datensätze zuornden kann?

Gruß aus dem sonnigen Garten
Frewer

RobertG
*******
Beiträge: 1845
Registriert: Fr, 13.04.2012 19:28
Kontaktdaten:

Re: Bericht 2spaltig

Beitrag von RobertG »

Hallo Frewer,

T1, T2 usw. sind ja die Aliasbezeichnungen für Abfragen, auf die dadurch wie auf Tabellen zugegriffen wird.
Ich schreibe immer gerne die gesamte Zuordnung als LEFT JOIN. Das bedeutet, dass ich zuerst die Tabelle nehme, die auf jeden Fall zu allen Datensätzen einen Wert enthält. Das ist in diesem Fall der Counter. Der ist ja so gestrickt, dass er entweder so viele Werte wie T2 oder so viele Werte wie T3 enthält. Da hänge ich dann alle anderen "Tabellen" dran.
Das ist bei dem Beispiel so wie ein Hauptformular mit mehreren Unterformularen. Schließlich bezieht sich T3 auch direkt auf T1. Würde sich T3 auf T2 beziehen, dann würden aus T3 nur die Daten angezeigt, deren "lfdNr." denen von T2 entsprechen. Das wäre dann so etwas wie eine Formular - Unterformular - UnterUnterformular - Konstruktion.
Wenn Du jetzt noch eine weitere Unterabfrage als T4 hinzufügen willst, so musst Du sicherstellen, dass T1 auch deren Datensätze alle erfasst. Ansonsten würden von T4 eben maximal so viele Datensätze angegeben, wie mit dem Counter übereinstimmen. Mehr auf keinen Fall.

Gruß

Robert

Frewer
**
Beiträge: 34
Registriert: Do, 05.03.2020 16:10

Re: Bericht 2spaltig

Beitrag von Frewer »

Hallo,
nun habe ich den Bericht zusammen mit Ansteuerung durch Formular + Hauptformular einen großen Schritt weitergebracht. Die beiden Spalten funktionieren wie gewünscht. Im Berichtsfuß habe ich nach Vorgabe Handbuch für jede Spalte ein Feld eingebracht, das die jeweilige Summe der Spalten enthalten soll. Das funktioniert mit =[SummeKostenABericht] bzw =[SummeKostenEBericht] auch gut bei der langen Spalte "E" (mehr aktive Zeilen), bei der Spalte "A" (weniger aktive Zeilen) steht 0,00.
Das verstehe ich zunächst nicht, da die Summierung mit 0 trotzdem einen Wert ergeben muss. Es sei denn in den leeren Zellen steht "NICHTS", das die Summierung jeweils löscht.

Gibt es dazu einen Rat?

Schöne Ostergrüße
Frewer

RobertG
*******
Beiträge: 1845
Registriert: Fr, 13.04.2012 19:28
Kontaktdaten:

Re: Bericht 2spaltig

Beitrag von RobertG »

Hallo Frewer,

die Formel für die Summierung ist ja nichts anderes als: Merke Dir das bisher errechnete und zähle das Hinzu, was jetzt noch zusätzlich in der Zeile steht. Und bei der kürzeren Spalte wird da wohl tatsächlich mehrmals einfach nichts stehen, weil die Abfrage keine Inhalt abgibt, oder? Und genau damit kann die Funktion des ReportBuilders nichts anfangen.

Zwei Lösungsmöglichkeiten:
Du baust die Ansicht, auf der der Bericht aufbaut, so aus, dass Du in den fehlenden Zeilen 0 stehen hast, oder Du ziehst Schlicht in einer Abfrage, die Du als letzte Spalte dran hängst, jeweils die Summen über die Spalten und rechnest nicht im ReportBuilder. Ich lasse grundsätzlich lieber Sachen durch die Datenbank erledigen als durch den ReportBuilder. Die Datenbank ist da wesentlich schneller. Nur wenn Du so etwas mit Übertrag konstruierst, dann hast Du natürlich Teilbeträge, die vom Layout abhängig sind. Und das lässt sich nicht über die Datenbank vorher regeln.

Gruß

Robert

Frewer
**
Beiträge: 34
Registriert: Do, 05.03.2020 16:10

Re: Bericht 2spaltig

Beitrag von Frewer »

Hallo Robert,
zunächst vielen Dank für Erklärung und Vorschläge. Habe also richtig vermutet mit dem Problem SUM() und leeren Zeilen.
Habe Deinen Vorschlag - letzte Spalte dranhängst - versucht umzusetzen, bin aber ziemlich im "Wald":
Mein SQL:
<</*
SELECT "T1"."Counter" AS "ctr", "T2"."Text" AS "ArtA", "T2"."Kosten" AS "KostenA", "T3"."Text" AS "ArtE", "T3"."Kosten" AS "KostenE", "T4"."SummeA" AS "SumA", "T5"."SummeE" AS "SumE"
FROM
( SELECT DISTINCT CASE
WHEN SELECT COUNT( "ID" ) ........
END AS "Counter" FROM "Abfrage1" AS "a" WHERE "dbok" = 0 ) AS "T1"
LEFT JOIN
( SELECT ( SELECT COUNT( "ID" ) FROM "Abfrage1" WHERE "Wer" = 'A' AND "dbok" = 0 AND "ID" <= "a"."ID" ) AS "lfdNr.", "a".* FROM "Abfrage1" AS "a" WHERE "a"."Wer" = 'A' AND "a"."dbok" = 0 ) AS "T2" ON "T1"."Counter" = "T2"."lfdNr."
LEFT JOIN
( SELECT ( SELECT COUNT( "ID" ) FROM "Abfrage1" WHERE "Wer" = 'E' AND "dbok" = 0 AND "ID" <= "a"."ID" ) AS "lfdNr.", "a".* FROM "Abfrage1" AS "a" WHERE "a"."Wer" = 'E' AND "a"."dbok" = 0 ) AS "T3" ON "T1"."Counter" = "T3"."lfdNr."

LEFT JOIN
( SELECT ( SELECT SUM( "Kosten" ) FROM "Abfrage1" WHERE "Wer" = 'A' AND "dbok" = 0 ) AS "SummeA", "a".* FROM "Abfrage1" AS "a" WHERE "a"."dbok" = 0 ) AS "T4" ON "T1"."Counter" = "T4"."SummeA"
LEFT JOIN
( SELECT ( SELECT SUM( "Kosten" ) FROM "Abfrage1" WHERE "Wer" = 'E' AND "dbok" = 0 ) AS "SummeE", "a".* FROM "Abfrage1" AS "a" WHERE "a"."dbok" = 0 ) AS "T5" ON "T1"."Counter" = "T5"."SummeE"
*/>>

Ergebnis: 2 neue Spalten SumA, SumE aber ohne Inhalt.
Für mich sind die beiden "ON .." Bedingungen unsinnig aber ohne sie geht gar nichts.
Setze ich << ON "T1"."Counter" = "T2"."lfdNr." >> und ON "T1"."Counter" = "T3"."lfdNr." (und natürlich auch in der einleitenden SELECT die Spalten T2.SummeA, T3.SummeE), dann kriege ich 196 (=4*7*7 wobei 4Zeilen KostenA, 7 Zeilen KostenE) Zeilen mit den beiden Summen-Spalten, die in jeder Zeile den richtigen GesamtSummenwert der Spalte "Kosten" beinhalten. Entferne ich die ON Bedingungen für T4, T5 dann Syntax-Fehler, leider nicht analysierbar, wie es in Eurem Handbuch steht.

Also ich experimentieren mit dem SQL Befehlen herum, erkenne aber noch keine Logik. Habe auch probiert gleich zu Beginn die Tabelle T1 durch 2 Spalten zu erweitern, um nach der Auswahl des Counters sofort die Summen zu bilden aber auch das endete in Syntax-Fehlern ohne Unterstützung des "Wo".

Bin sicher, dass Du sofort eine Lösung in Deinem Repertoire hast an der ich natürlich sehr interessiert bin.

Gruß
Frewer

RobertG
*******
Beiträge: 1845
Registriert: Fr, 13.04.2012 19:28
Kontaktdaten:

Re: Bericht 2spaltig

Beitrag von RobertG »

Ich versuche das einmal in die entsprechende Form zu bringen:

Code: Alles auswählen

SELECT "T1"."Counter" AS "ctr", "T2"."Text" AS "ArtA", "T2"."Kosten" AS "KostenA", "T3"."Text" AS "ArtE", "T3"."Kosten" AS "KostenE",  
( SELECT SUM( "Kosten" ) FROM "Abfrage1" WHERE "Wer" = 'A' AND "dbok" = 0 ) AS "SumA", 
( SELECT SUM( "Kosten" ) FROM "Abfrage1" WHERE "Wer" = 'E' AND "dbok" = 0 ) AS "SumE"
FROM
( SELECT DISTINCT CASE
WHEN SELECT COUNT( "ID" ) ........
END AS "Counter" FROM "Abfrage1" AS "a" WHERE "dbok" = 0 ) AS "T1"
LEFT JOIN
( SELECT ( SELECT COUNT( "ID" ) FROM "Abfrage1" WHERE "Wer" = 'A' AND "dbok" = 0 AND "ID" <= "a"."ID" ) AS "lfdNr.", "a".* 
FROM "Abfrage1" AS "a" WHERE "a"."Wer" = 'A' AND "a"."dbok" = 0 ) AS "T2" ON "T1"."Counter" = "T2"."lfdNr."
LEFT JOIN
( SELECT ( SELECT COUNT( "ID" ) FROM "Abfrage1" WHERE "Wer" = 'E' AND "dbok" = 0 AND "ID" <= "a"."ID" ) AS "lfdNr.", "a".* 
FROM "Abfrage1" AS "a" WHERE "a"."Wer" = 'E' AND "a"."dbok" = 0 ) AS "T3" ON "T1"."Counter" = "T3"."lfdNr."
Die Summen werden direkt durch eine Abfrage berechnet. Die Abfrage ergibt ja nur einen Datensatz und kann deshalb auch so in die Feldauflistung geschrieben werden.

Gruß

Robert

Frewer
**
Beiträge: 34
Registriert: Do, 05.03.2020 16:10

Re: Bericht 2spaltig

Beitrag von Frewer »

Hi Robert,
es haut mich um. Es funktioniert perfekt. Vielen Dank. Der Bericht ist fertig und funktioniert.

Aber ich bin noch etwas entfernt vom Verstehen und bitte Dich, mir zu helfen.
Meine Frage:
1. Wenn ich die Tabelle "T1" nehme, dann steht da nach dem SELECT "T1"."Counter" AS "ctr",....., (SELECT SUM("Kosten") ........) AS "SumA". Und tatsächlich wird hinter T3 SumA und SumE angefügt. Warum benötige ich für den Counter den T1-Alias nicht aber für SumA, SumE ? Ich habe es probiert, gibt aber Syntax-Fehler.
2. Im Handbuch lese ich die Bedeutung von "JOIN", "LEFT JOIN", "Right JOIN" und Komma. Kann man Right JOIN durch ein Komma ersetzen? Offenbar nicht, denn sofort wird ein Syntax error gemeldet. Warum kann ich dann SumA und SumE einfach durch ein Komma anhängen, das Zählen der Zeilen COUNT("ID") aber nicht und warum reicht hier das Komma nicht?

Ich hätte nie gedacht, dass mein "kleine" Idee so schwierig ist zu lösen wobei ich glaube, dass die Spek für viele Anwendungen gültig ist.
Deshalb nochmals vielen Dank für die Denksportaufgabe mit Lösung

Gruß
Frewer

RobertG
*******
Beiträge: 1845
Registriert: Fr, 13.04.2012 19:28
Kontaktdaten:

Re: Bericht 2spaltig

Beitrag von RobertG »

Hallo Frewer,

Zu 1.: Die Berechnung der Summe erfolgt für die ganze Tabelle und ermittelt nur einen Wert. Du hast in der Abfrage in jeder Zeile jetzt die gleiche Summe stehen. Der Counter muss natürlich von Zeile zu Zeile unterschiedlich sein.

Zu 2.: Da weiß ich gar nicht, an welcher Stelle im Handbuch Du bist. Was hat das Komma mit einem Join zu tun?

Gruß

Robert

Frewer
**
Beiträge: 34
Registriert: Do, 05.03.2020 16:10

Re: Bericht 2spaltig

Beitrag von Frewer »

Hi Robert,
Zu 1.
Bin immer noch am Studieren. Verstehe zwar Deinen Unterschied zwischen SUM() und COUNT(), hänge aber an der Logik, wie das Programm vorgeht. Vielleicht muss ich halt doch noch viel tiefer einsteigen, um zu verstehen, dass durch das (SELECT SUM() FROM .. die Tabelle total durchlaufen wird (natürlich in den Grenzen von WHERE) und summiert. Wenn hier COUNT() stehen würde, wäre das Ergebnis die Anzahl der durchlaufenen Zeilen. Jetzt reichen die Alias "SumA" zur Darstellung. Warum geht das aber nicht zB beim 1ten COUNT(), denn hier steht "AS Counter" und dann plötzlich AS "T1".
Zu 2.
Ich habe folgenden Text auf Seite 266 im Handbuch V 62-1 gefunden:
"Werden die Tabellen durch einen JOIN statt durch ein Komma miteinander verbunden, so wird die Beziehung der Tabellen zueinander direkt nach der jeweils folgenden Tabelle mit dem Begriff ON beginnend definiert."
Für mich hat sich das so gelesen, wie wenn JOIN und Komma gleiche Wirkung haben, bei JOIN aber der Zusatzbegriff ON ... hinzukommen muss (Einbinden der Tabellen).

Na ja ich werde mich weiter im Buch vertiefen, habe ja doch schon viele Dinge gefunden, die Du mir bereits als Lösung genannt hast. Es ist aber schwierig ohne direkten Bezug die einzelnen Passagen zu verstehen und mit der eigenen Überlegung abzustimmen.

Schönen Abend bei herrlicher Sonne
Frewer

RobertG
*******
Beiträge: 1845
Registriert: Fr, 13.04.2012 19:28
Kontaktdaten:

Re: Bericht 2spaltig

Beitrag von RobertG »

Hallo Frewer,

noch einmal zu 1.: Wenn Du da ein COUNT einsetzt geht das natürlich auch - aber nur für alle Zeilen. Wenn Du das Ganze mitlaufen lassen willst, so dass die Zahlen hochgezählt werden, dann musst Du die Abfrage als korrelierende Unterabfrage starten. Sie hat etwas mit der äußeren Abfrage zu tun. Die Gesamtsumme oder die Gesamtanzahl aber nicht.

zu 2: Da muss ich den Text mir noch einmal ansehen. Es geht darum, dass einmal Tabelle durch Kommata getrennt werden und anschließend eine WHERE-Verbindung der Tabelle erfolgt. Bei einem Join erscheint erst einmal eine Tabelle, dann die Join-Verbindung zur zweiten Tabelle und dann die Bedingung der Verbindung mit ON ... und danach dann die nächste Tabelle usw. Wenn Du also mit Kommas arbeitest, dann wählst Du gerade keinen Join.

Ich habe für das nächste Update "statt durch ein Komma" ersatzlos rausgenommen. Tabellen werden ja durch das Komma gerade nicht miteinander verbunden. Die Verbindung kommst erst später mit WHERE ...

Gruß

Robert

Frewer
**
Beiträge: 34
Registriert: Do, 05.03.2020 16:10

Re: Bericht 2spaltig

Beitrag von Frewer »

Hallo Robert,

ok, danke, das habe ich verstanden und den Unterschied zwischen JOIN und Komma ebenfalls.
Mit meinem Bericht bin ich weiter oder besser fast fertig. Mit dem Assistenten zu arbeiten ist aber ganz schön Nerv tötend und auch ich kann nur empfehlen, jedfen Schritt zu speichern. Und trotzdem verstellen sich immer wieder Werte, so dass Felder in der Designansicht plötzlich nicht mehr zu sehen sind, weil die Bereichs-Höhe auf 22.000,00cm steht. Na ja.
Den Ausdruck habe ich noch nicht weiter untersucht. Dazu werde ich die Datenbank auf meinen LapTop mit WIN10 und LibreOffice kopieren. Es ist mir gelungen, meinen alten Drucker HP Laserjet 1010 mit WIN10 zu betreiben obwohl HP keinen Treiber mehr dafür bereit hält.
"Probieren geht über Studieren"!!
Meine Druckversuche mit meinem WIN XP und OpenOffice PC wollen nicht klappen, obwohl ich über den Menübefehl den bericht anstandslos drucken kann. Mal sehen.
Über meine Fortschritte werde ich natürlich berichten. Als ebenfalls Beamter ist Dir sicher aufgefallen, wozu das Ganze dient, nämlich zur Abrechnung von Beihilfe und DeBeKa. Vielleicht ein bisschen umständlich in SmartPhone-Zeiten aber halt eine andere Art der Lösung.

Gruß
Frewer

Antworten