AMPLECOMPUTERS
asked on
Invalid use of group function, but there is no grouping
I am converting an Access front end database from using a MSSQL backend to a MySQL backend and one of my SQL statement conversions is not going so well. The original code generated by Access was this:
I made some changes for the conversion, fixed some gripes by Workbench, and wound up with this:
Now I am at a standstill because it is complaining about a group function where there is no group function.
Any help appreciated.
Update [Statements To Print]
Set [Partial]=(Select isnull(Sum(Payment.[Payment Amount]), 0)
from Payment
Inner Join Orders On Orders.[Order Number]=Payment.[Invoice Number]
INNER JOIN [Statements To Print] ON Orders.[Customer Number]=[Statements To Print].[Invoice Number]
Where Orders.Paid = 0
And Orders.Deleted = 0
and orders.[Customer Number]='0'
I made some changes for the conversion, fixed some gripes by Workbench, and wound up with this:
Update StatementsToPrint
INNER JOIN Orders
ON StatementsToPrint.InvoiceNumber = Orders.OrderNumber
INNER JOIN Payment
ON Payment.InvoiceNumber=Orders.InvoiceNumber
Set StatementsToPrint.Partial=Sum(Payment.PaymentAmount)
Where Orders.Paid = 0
And Orders.Deleted = 0
and orders.CustomerNumber='0'
Now I am at a standstill because it is complaining about a group function where there is no group function.
Any help appreciated.
Sum is a "group" function.
But why not use the original that works? You just need to replace IsNull with an IIf( ... Is Null ..) replacement.
/gustav
But why not use the original that works? You just need to replace IsNull with an IIf( ... Is Null ..) replacement.
/gustav
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
IIF?
MySQL
For MySQL you use IFNULL()
MySQL
For MySQL you use IFNULL()
my apologies I missed the MySQL in your question (thinking mssql)
You definitely need IFNULL(), just try changing isnull in your original to IFNULL
(in MySQL isnull is quite different)
You definitely need IFNULL(), just try changing isnull in your original to IFNULL
(in MySQL isnull is quite different)
ASKER
1) I can not use the original code as switching from IsNull to IfNull causes the code in Access to break (Access cant process the IfNull code) in VBA. I can't move it to a pass-through query as this line is executed in a recordset loop where the customer number is incremented (within available customer numbers in the table) and I do not know how to pass a variable to a query in Access.
2) Yes, [Statements To Print].[Invoice Number] = Orders.[Customer Number] is obviously a typo, sorry about that, not enough caffeine this morning.
3) Since Sum(Payment.PaymentAmount) is an aggregate function, how do I get passed this?
2) Yes, [Statements To Print].[Invoice Number] = Orders.[Customer Number] is obviously a typo, sorry about that, not enough caffeine this morning.
3) Since Sum(Payment.PaymentAmount)
If you still run the query in VBA and Access SQL, you shouldn't have to change anything.
What issues did you meet with the original query?
/gustav
What issues did you meet with the original query?
/gustav
Rather than using the IsNull or IfNull functions, you might try using a case statement instead, which should be acceptable to VBA, MSSQL, and MySQL.
Select case when Sum(Payment.[Payment Amount]) is null then 0 else Sum(Payment.[Payment Amount]) end
Select case when Sum(Payment.[Payment Amount]) is null then 0 else Sum(Payment.[Payment Amount]) end
> .. should be acceptable ...
That's for SQL Server. It won't work in Access SQL.
IsNull works fine for any db backend with an ODBC connection. To speed it up, if needed, use as I wrote an IIf( .. Is Null ...) statement.
/gustav
That's for SQL Server. It won't work in Access SQL.
IsNull works fine for any db backend with an ODBC connection. To speed it up, if needed, use as I wrote an IIf( .. Is Null ...) statement.
/gustav
I know that case will not work in Access, but it is acceptable in VBA as I stated. I based my response on the asker's statement -
>>1) I can not use the original code as switching from IsNull to IfNull causes the code in Access to break (Access cant process the IfNull code) in VBA<<
>>1) I can not use the original code as switching from IsNull to IfNull causes the code in Access to break (Access cant process the IfNull code) in VBA<<
Ah, well, yes, but this thread has been about (Access) SQL not VBA.
/gustav
/gustav
is an aggregate function requireing group by
line 6 immediately above