0
I’m trying to make an SQL query in Firebird where it will only return data if the sum of the fields in the first table is different from the Field "vlrnota" of the second table. However, when trying to rotate the SELECT, error
Invalid Expression in the select list (not contained in either an Aggregate Function or the
GROUP BYclause)
SELECT sum((vlrunit*qtdade)+VLRICMSUBSTITUICAO+d.vlripi+d.vlriss-valdesconto+valacrescimo)soma,
d.numero,
vlrnota
FROM MOVIMENTACAO m
left join documentos d
on m.iddoc = d.iddoc
Group by vlrnota
HAVING VLRNOTA = (SELECT sum((vlrunit*qtdade)+VLRICMSUBSTITUICAO+d.vlripi+d.vlriss-valdesconto+valacrescimo) AS SOMA FROM MOVIMENTACAO)
Lucas*, when Voce makes a sum or another function that unites information, all fields that are in SELECT without being a SUM or COUNT need to be within GROUP BY, in which case Voce needs to enter the field d.numero, because the sum of it is in Select within HAVING, and in the case of "Main" Select it as SOMA and yes as a normal field. Test just put d.numero in your Group By. If you still don’t work there are other possible errors within your Select that I can help you with.
– Gigliotti
Gigliotti, I did what Voce said, but now gave another error: "Dynamic SQL Error SQL error code = -104 Invalid Expression in the CLAUSE having (neither an Aggregate Function nor a part of the GROUP BY clause)"
– Lucas Trindade Langaro
In this case as I commented that there could be another error that I believe is in your HAVING, which works to "filter" from a sum, which in the case Voce did within the Select or is has no sum your select generates a pure number, so you can try to exchange HAVING for WHERE or remove Select after HAVING and leave only your SUM
– Gigliotti