Solved

Invalid use of group function, but there is no grouping

Posted on 2013-06-24
12
551 Views
Last Modified: 2013-06-24
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:

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'

Open in new window


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'

Open in new window


Now I am at a standstill because it is complaining about a group function where there is no group function.

Any help appreciated.
0
Comment
Question by:AMPLECOMPUTERS
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39271415
Sum(Payment.PaymentAmount)

is an aggregate function requireing group by

line 6 immediately above
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 39271434
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
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39271448
maybe this will help?
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]
        WHERE [Statements To Print].[Invoice Number] = Orders.[Customer Number]
            AND Orders.Paid = 0
            AND Orders.Deleted = 0
            AND orders.[Customer Number] = '0'
        )

Open in new window

this is based on the 'original code'
but I have my doubts about this join:  invoice number = customer number (? is that right)
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 48

Expert Comment

by:PortletPaul
ID: 39271455
IIF?

MySQL

For MySQL you use IFNULL()
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39271478
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)
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39271493
MySQL docs:

ISNULL()
this takes just one parameter

IFNULL()
this takes 2 parameters:
If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2. IFNULL() returns a numeric or string value, depending on the context in which it is used.
0
 

Author Comment

by:AMPLECOMPUTERS
ID: 39271539
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?
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 39271692
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
0
 
LVL 32

Expert Comment

by:awking00
ID: 39271695
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
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 39271725
> .. 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
0
 
LVL 32

Expert Comment

by:awking00
ID: 39271806
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<<
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 39271834
Ah, well, yes, but this thread has been about (Access) SQL not VBA.

/gustav
0

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question