[opgelost] Functie Som werkt niet in Query

Bespreek het databaseprogramma
PeterSimons
Berichten: 11
Lid geworden op: vr sep 25, 2015 9:26 pm

[opgelost] Functie Som werkt niet in Query

Bericht door PeterSimons »

Na jaaaren uitstel ben ik dan toch eindelijk begonnen mijn bedrijfsadministratie (ZZP) over te zetten van Access naar LibreOffice. Het valt me niet tegen: de rapport generator werkt, de tabellen, formulieren en querys zitten er in.
Voor de factuur is er een query die o.a. het totaalbedrag moet uitrekenen. Maar ik krijg de optelling niet aan de gang: zodra ik de functie Som aanzet, krijg ik een foutmelding:

SQL-status: 37000
Foutcode: -67

Not in aggregate function or group by clause: org.hsqldb.Expression@5773a483 in statement [SELECT "Factuur"."FactuurNr", ...etc.

Geen enkele functie doet het, dus ik vermoed dat het probleem in de installatie zit?

Groetjes,
Peter
Laatst gewijzigd door PeterSimons op di sep 29, 2015 8:15 pm, 1 keer totaal gewijzigd.
LibreOffice 4.2 op Linux Mint 17.1
RPG
Berichten: 4667
Lid geworden op: wo apr 15, 2009 1:01 am
Locatie: Apeldoorn, Nederland

Re: Functie Som werkt niet in Query

Bericht door RPG »

Hallo

Gebruik geen embedded database voor je bedrijfsadministratie. Kijk naar deze link. Zorg ook voor een goede kopie.

De rapport generator kan slechts een simpele query hanteren. Wel is het mogelijk om met een ingebouwde functie van de generator de som te berekenen van velden. De fout lijkt mij niet het resultaat van een verkeerde installatie.

Rapport generator
Op een bepaald moment is LibreOffice standaard overgegaan naar wat heet Sun/Oracle Report Builder. In mijn versie schijnt zelfs de help die daarbij hoort ingebouwd te zijn. Anders is het hier te vinden. Als je kleurtjes ziet tijdens het maken van een rapport dan heb je de nieuwe Rapport generator.

Romke
LibreOffice 7.4.3.2 op openSUSE Leap 15.4
PeterSimons
Berichten: 11
Lid geworden op: vr sep 25, 2015 9:26 pm

Re: Functie Som werkt niet in Query

Bericht door PeterSimons »

Hoi Romke,
Split database... Logisch! Dat zal ik doen!

Ik heb de nieuwe raport generator geinstalleerd. Dat was nodig, omdat na de gewone installatie alleen de wizard voor rapporten werkte. Een rapport maken in de ontwerpweergave was niet mogelijk.
LibreOffice 4.2 op Linux Mint 17.1
RPG
Berichten: 4667
Lid geworden op: wo apr 15, 2009 1:01 am
Locatie: Apeldoorn, Nederland

Re: Functie Som werkt niet in Query

Bericht door RPG »

Hallo

Hier is de Sun Report Builder handleiding. Voorzover ik weet maakt het niet uit of het voor LibreOffice gebruikt wordt.

Romke
LibreOffice 7.4.3.2 op openSUSE Leap 15.4
PeterSimons
Berichten: 11
Lid geworden op: vr sep 25, 2015 9:26 pm

Re: Functie Som werkt niet in Query

Bericht door PeterSimons »

Dank je Romke, voor de handleiding. De Sun Report Builder had ik al aan de praat gekregen, een mooi programma.

Maar de vraag blijft: waarom doen functies (waaronder de functie Sum/som) het niet in een Base-query. Ofwel; hoe kan ik de foutmelding
Base schreef:SQL-status: 37000 Foutcode: -67 Not in aggregate function or group by clause:
oplossen....
LibreOffice 4.2 op Linux Mint 17.1
RPG
Berichten: 4667
Lid geworden op: wo apr 15, 2009 1:01 am
Locatie: Apeldoorn, Nederland

Re: Functie Som werkt niet in Query

Bericht door RPG »

Hallo

Laten we de vraag los koppelen van de report builder. Ik koppel het los van ReportBuilder omdat de meeste queries met een som functie redelijker wijs niet bedoeld kunnen zijn voor RB. Dan blijft over dat de query verkeerd is opgebouwd. Ik kan de query niet zien dus kan er ook niets over zeggen.

Mijn advies is bouw een query langzaam op. Op die manier weet je precies in welk onderdeel de fout precies zit.

Bij een zoekactie vond ik dit. Ik weet niet of dat je helpt. Maar feitelijk gaat dat over het langzaam met kennis opbouwen van een query. Heel veel zaken in een query moeten langzaam opgebouwd worden. Ook enige kennis van de SQL taal is belangrijk.

Romke
LibreOffice 7.4.3.2 op openSUSE Leap 15.4
PeterSimons
Berichten: 11
Lid geworden op: vr sep 25, 2015 9:26 pm

Re: Functie Som werkt niet in Query

Bericht door PeterSimons »

Hoi Romke,
Ik heb jaren met Crystal Reports rapporten gemaakt voor ERP-systemen. Hoewel CR veel berekeningen kan maken, deed ik die berekeningen liever in de SQL, dan kon ik zien wat er precies gebeurde. Maar als dat niet standaard is, dan doen we het in Base anders...

Los van deze methodiek, doet de query iets niet, waarvan ik denk dat het wel moet werken. Ik zal twee voorbeelden geven:

Deze werkt:
SELECT "Factuur"."FactuurNr", "FactuurRegel"."FactuurRegelNr", "FactuurRegel"."FactRegTarief" FROM "Factuur", "FactuurRegel" WHERE "Factuur"."FactuurNr" = "FactuurRegel"."FactuurNr" AND "Factuur"."FactuurNr" = :factuur ORDER BY "Factuur"."FactuurNr" ASC, "FactuurRegel"."FactuurRegelNr" ASC

Als ik de kolom FactuurRegel.FactRegTarief als som wil zien, dan werkt het niet meer:
SELECT "Factuur"."FactuurNr", "FactuurRegel"."FactuurRegelNr", SUM( "FactuurRegel"."FactRegTarief" ) FROM "Factuur", "FactuurRegel" WHERE "Factuur"."FactuurNr" = "FactuurRegel"."FactuurNr" AND "Factuur"."FactuurNr" = :factuur ORDER BY "Factuur"."FactuurNr" ASC, "FactuurRegel"."FactuurRegelNr" ASC

Som doet het niet, maar ook Aantal en zo werken geen van allen.

Ik hoop dat je er iets mee kan!

Groetjes,
Peter
LibreOffice 4.2 op Linux Mint 17.1
RPG
Berichten: 4667
Lid geworden op: wo apr 15, 2009 1:01 am
Locatie: Apeldoorn, Nederland

Re: Functie Som werkt niet in Query

Bericht door RPG »

Hallo

Ik denk dat dit het antwoord is:
Sliderule schreef:Explanation2: Important note: this is called an Aggregate Query because you are asking the database to perform some calculations, such as, SUM, MIN, MAX etc. . . . therefore . . . for EACH column in the SELECT clause, it must EITHER include an Aggregate function OR be included in the GROUP BY clause. :bravo:
Het is de eerste draad in deze zoekactie.

Misschien geeft Eremmel nog een mooie uitleg maar ik kan soms de fout vinden maar het geven van een mooie uitleg is te moeilijk en dan zoek ik hier iets voor op.

Hier staat een lijst met tutorials. SQL is een mooi middel om informatie te halen uit de gegevens die beschikbaar zijn. De laatste link daar is in het Engels. Ik heb dat een mooie uitleg gevonden.

Mogelijk werkt dit?:

Code: Selecteer alles

SELECT
	"Factuur"."FactuurNr",
	"FactuurRegel"."FactuurRegelNr",
	SUM( "FactuurRegel"."FactRegTarief" )
FROM "Factuur", "FactuurRegel"
WHERE	"Factuur"."FactuurNr" = "FactuurRegel"."FactuurNr"
		AND
	"Factuur"."FactuurNr" = :factuur
  GROUP BY "Factuur"."FactuurNr" , "FactuurRegel"."FactuurRegelNr"
Romke
LibreOffice 7.4.3.2 op openSUSE Leap 15.4
PeterSimons
Berichten: 11
Lid geworden op: vr sep 25, 2015 9:26 pm

Re: Functie Som werkt niet in Query

Bericht door PeterSimons »

OK, ik snap dat als je een functie gebruikt, het programma moet weten binnen welke groepering de functie uitgevoerd moet worden.

Je veranderde query werkt, dat wil zeggen dat de foutmelding niet verschijnt. Maar omdat de groep "FactRegelNr" bestaat, gaat hij per factuurregelNr een optelling maken. Die is natuurlijk gelijk aan FactuurRegelTarief, omdat elke factuurregel maar 1 tarief is.

Als ik de Group By verander naar enkel FactuurNr (dan zou ik verwachten dat de optelling per factuurnummer gedaan wordt), dan verschijnt de foutmelding weer. En dat snap ik niet; elk record heeft een factuurnummer, en valt dus binnen de Group By "Factuur"."FactuurNr"?
LibreOffice 4.2 op Linux Mint 17.1
RPG
Berichten: 4667
Lid geworden op: wo apr 15, 2009 1:01 am
Locatie: Apeldoorn, Nederland

Re: Functie Som werkt niet in Query

Bericht door RPG »

Hallo

Helaas begrijp ik nu niet meer wat het probleem is. Wanneer de door mij aangewezen oplossing niet de gewenste oplossing biedt dan heb ik het idee dat de query een grote tabel moet afleveren en dat de rapport generator de optelling moet doen. Zoveel meer keuzes lijken mij er niet te zijn. Er zal vast en zeker na enig denk werk ook wel een query te bedenken zijn die alles doet maar dat weet ik niet. Je kunt ook proberen het rapport te maken in calc maar dat levert weer andere problemen. Calc biedt een gemakkelijke uitwisseling van gegevens door middel van de gegevensbronnen.

De rapport generator laat alles mooi in groepjes verdelen. Het is ook mogelijk om die groepjes op te tellen.

Een discussie over de zin of onzin van de werking van group heeft met mij weinig zin want ik accepteer altijd de werking zoals die is. Ik kan daar toch niets aan veranderen.

Romke
LibreOffice 7.4.3.2 op openSUSE Leap 15.4
PeterSimons
Berichten: 11
Lid geworden op: vr sep 25, 2015 9:26 pm

Re: Functie Som werkt niet in Query

Bericht door PeterSimons »

Ik vind het prima om de optelling in de Report Generator te laten plaatsvinden. Dat gaat zeker werken.

Mijn vraag nu is hoe de door jou gegeven query aangepast moet worden om een optelling te maken die alle records FactRegelTarief per FactuurNr optelt. Ik krijg een foutmelding als ik Group By op Factuur.FactuurNr zet in plaats van Factuur.FactuurNr , FactuurRegel.FactuurRegelNr.
Bij een Group By Factuur.FactuurNr , FactuurRegel.FactuurRegelNr is de optelling steeds gelijk aan FactuurRegel.FactRegelTarief. Zeg maar een optelling 12 =12. Of 3=3. ;-)
LibreOffice 4.2 op Linux Mint 17.1
RPG
Berichten: 4667
Lid geworden op: wo apr 15, 2009 1:01 am
Locatie: Apeldoorn, Nederland

Re: Functie Som werkt niet in Query

Bericht door RPG »

Hallo

Ik kan niet meer bedenken dan group in het geheel weglaten evenals sorteren en eventueel nog meer dingen alleen de gegevens in een enkele lange tabel en de rest wordt dan in de rapport generator gedaan.

Romke
LibreOffice 7.4.3.2 op openSUSE Leap 15.4
eremmel
Berichten: 670
Lid geworden op: di sep 01, 2009 10:11 am
Locatie: Barneveld, Nederland

Re: Functie Som werkt niet in Query

Bericht door eremmel »

PeterSimons schreef:Ik vind het prima om de optelling in de Report Generator te laten plaatsvinden. Dat gaat zeker werken.

Mijn vraag nu is hoe de door jou gegeven query aangepast moet worden om een optelling te maken die alle records FactRegelTarief per FactuurNr optelt. Ik krijg een foutmelding als ik Group By op Factuur.FactuurNr zet in plaats van Factuur.FactuurNr , FactuurRegel.FactuurRegelNr.
Bij een Group By Factuur.FactuurNr , FactuurRegel.FactuurRegelNr is de optelling steeds gelijk aan FactuurRegel.FactRegelTarief. Zeg maar een optelling 12 =12. Of 3=3. ;-)
Als je alle FactRegelTarief wilt optellen voor een Factuur.FactuurNr, dan moet je ook niet in de SELECT vragen om FactuurRegel.FactuurRegelNr, want die is nu invariant.

Dus je query moet worden:

Code: Selecteer alles

SELECT
   "Factuur"."FactuurNr",
   SUM( "FactuurRegel"."FactRegTarief" ) AS "FactuurTot"
FROM "Factuur", "FactuurRegel"
WHERE   "Factuur"."FactuurNr" = "FactuurRegel"."FactuurNr"
      AND   "Factuur"."FactuurNr" = :factuur
  GROUP BY "Factuur"."FactuurNr" 
P.S.1.
Als je nog steeds op Windows werkt, waarom dan niet gewoon je access database blijven gebruiken? De MS SQL Jet engine (die gebruikt je om queries uit te voeren op je access database) is best krachtig. Wil je LO gebruiken met MS Access en Oracle Report Builder, blijf dan voorlopig op 4.2.8.2 zitten (ik heb 5.x nog niet teste). Ik zelf heb een grote extensie boven op OROV (met MSAccess) gemaakt in AOO die werkte tot 3.3.x en in LO pas met versie 4.2.8.2.

P.S.2.
Als je ervaring met cognos en b.v Oracle / MS SQLServer hebt dan heb je misschien ook wel eens met running totals gespeeld etc. Als dat weer zo willen gebruiken vanuit je SQL, dan is overstappen op Postgresql een goed idee. De enige opensource database die ik ken die SQL Windowing functies ondersteund.
W11 21H2 (build 22000), LO 7.4.1.2(x64)
Het is Microsoft marketing die laat geloven dat computers geschikt zijn voor niet technici
PeterSimons
Berichten: 11
Lid geworden op: vr sep 25, 2015 9:26 pm

Re: Functie Som werkt niet in Query

Bericht door PeterSimons »

eremmel schreef:Als je alle FactRegelTarief wilt optellen voor een Factuur.FactuurNr, dan moet je ook niet in de SELECT vragen om FactuurRegel.FactuurRegelNr, want die is nu invariant.
Zo bereken je inderdaad het totaalbedrag van FactRegTarief gegroepeerd per FactuurNr, maar waarom kun je dat niet berekenen terwijl je ondertussen de factuurregels ook laat zien? De weergave van deze optelling moet natuurlijk in de groep "FactuurNr" in de footer.

Een factuur heeft een optelling nodig van de som van factuurregels met BTW-tarief 0 / 6 / 21% is, en diens BTW-bedragen. Ik wilde die voorbereiding in de query doen, zodat de report generator alleen de waarde van die kolommen in de report footer (of factuurnummer-groepering footer) hoeft te zetten.
eremmel schreef:P.S.1.
Als je nog steeds op Windows werkt, waarom dan niet gewoon je access database blijven gebruiken?
Ik heb enkel Linux Mint, en ben blij dat de Windows laptop het raam uit kan nu de laatste functionaliteit ook op Linux aan het (beginnen te ) werken is.
eremmel schreef:P.S.2.
Als je ervaring met cognos en b.v Oracle / MS SQLServer hebt dan heb je misschien ook wel eens met running totals gespeeld etc. Als dat weer zo willen gebruiken vanuit je SQL, dan is overstappen op Postgresql een goed idee. De enige opensource database die ik ken die SQL Windowing functies ondersteund.
Hmm, ik ben wel blij met de formulieren en report generator van Base...
LibreOffice 4.2 op Linux Mint 17.1
eremmel
Berichten: 670
Lid geworden op: di sep 01, 2009 10:11 am
Locatie: Barneveld, Nederland

Re: Functie Som werkt niet in Query

Bericht door eremmel »

Mogelijke oplossing, voeg een extra regel toe die het totaal bevat (niet afdrukken natuurlijk).

Code: Selecteer alles

SELECT
   "Factuur"."FactuurNr",
   "FactuurRegel"."FactuurRegelNr",
   "FactuurRegel"."FactRegTarief"
FROM "Factuur", "FactuurRegel"
WHERE   "Factuur"."FactuurNr" = "FactuurRegel"."FactuurNr"
      AND  "Factuur"."FactuurNr" = :factuur
 
UNION ALL

SELECT
   "Factuur"."FactuurNr",
   99999,
   SUM( "FactuurRegel"."FactRegTarief" )
FROM "Factuur", "FactuurRegel"
WHERE   "Factuur"."FactuurNr" = "FactuurRegel"."FactuurNr"
      AND  "Factuur"."FactuurNr" = :factuur
GROUP BY "Factuur"."FactuurNr" 

ORDER BY  "Factuur"."FactuurNr",   "FactuurRegel"."FactuurRegelNr"

Je kunt de tweede query ook als derived table gebruiken in een extra join conditie (misschien overstappen op direct SQL mode):

Code: Selecteer alles

SELECT
   "Factuur"."FactuurNr",
   "FactuurRegel"."FactuurRegelNr",
   "FactuurRegel"."FactRegTarief",
   A."FactuurTotaal"
FROM "Factuur" 
    INNER JOIN "FactuurRegel" ON   "Factuur"."FactuurNr" = "FactuurRegel"."FactuurNr"
WHERE  "Factuur"."FactuurNr" = :factuur
INNER JOIN (  
             SELECT "Factuur"."FactuurNr", SUM( "FactuurRegel"."FactRegTarief" ) AS "FactuurTotaal"
             FROM "Factuur"
                 INNER JOIN "FactuurRegel" ON   "Factuur"."FactuurNr" = "FactuurRegel"."FactuurNr"
             WHERE "Factuur"."FactuurNr" = :factuur
             GROUP BY "Factuur"."FactuurNr"  
) A ON "Factuur"."FactuurNr" = A."FactuurNr"
ORDER BY  "Factuur"."FactuurNr",   "FactuurRegel"."FactuurRegelNr"
Als je postgres gebruikt kun je het bovenstaande ook zo schrijven:

Code: Selecteer alles

SELECT
   "Factuur"."FactuurNr",
   "FactuurRegel"."FactuurRegelNr",
   "FactuurRegel"."FactRegTarief",
   SUM("FactuurRegel"."FactRegTarief") OVER ( PARTITION BY "Factuur"."FactuurNr" ) AS "FactuurTotaal"
FROM "Factuur"
    INNER JOIN "FactuurRegel" ON  "Factuur"."FactuurNr" = "FactuurRegel"."FactuurNr"
WHERE "Factuur"."FactuurNr" = :factuur
W11 21H2 (build 22000), LO 7.4.1.2(x64)
Het is Microsoft marketing die laat geloven dat computers geschikt zijn voor niet technici
PeterSimons
Berichten: 11
Lid geworden op: vr sep 25, 2015 9:26 pm

Re: Functie Som werkt niet in Query

Bericht door PeterSimons »

Dat laatste is inderdaad wat ik zoek. Maar vreemd dat deze in mijn ogen zo eenvoudige functie zo ingewikkeld moet worden...

Even iets aanverwants: ik heb besloten de totalen door Report Builder te laten berekenen. Daarvoor heb ik de query aangepast. Hoe vertel ik de report builder dat de query is aangepast, en wat de wijzigingen waren? (Zo heb ik ook twee velden samengevoegd tot 1 (met alias), dan moet de report builder natuurlijk ook weten...)

Peter
LibreOffice 4.2 op Linux Mint 17.1
eremmel
Berichten: 670
Lid geworden op: di sep 01, 2009 10:11 am
Locatie: Barneveld, Nederland

Re: Functie Som werkt niet in Query

Bericht door eremmel »

PeterSimons schreef:Dat laatste is inderdaad wat ik zoek. Maar vreemd dat deze in mijn ogen zo eenvoudige functie zo ingewikkeld moet worden...
Dat heeft te maken met de SQL taal definitie en de ontwikkelingen van de SQL taal over de afgelopen 20 jaar.
PeterSimons schreef:Even iets aanverwants: ik heb besloten de totalen door Report Builder te laten berekenen. Daarvoor heb ik de query aangepast. Hoe vertel ik de report builder dat de query is aangepast, en wat de wijzigingen waren? (Zo heb ik ook twee velden samengevoegd tot 1 (met alias), dan moet de report builder natuurlijk ook weten...)
Ik zou zelf ook moeten zoeken, maar meestal heb je een 'navigator' (F5) tot je beschikking daarmee kun je op verschillende niveaus kijken naar je properties (en je query zoeken).
Ik kan me van vroeger herinneren dat de query in eerste instantie gekopieerd wordt naar je rapport (en dat wil je niet) want je wilt dat je SQL uit je query sectie van je Base document gelezen wordt.
W11 21H2 (build 22000), LO 7.4.1.2(x64)
Het is Microsoft marketing die laat geloven dat computers geschikt zijn voor niet technici
Plaats reactie