Each group by expression must contain at least one column that is not an outer reference

I have an append query that generates the following error message:

ODBC--call failed.
[Microsoft][ODBC SQL Server Driver][SQL Server] Each group by expression must contain at least one column that is not an outer reference. (#164)

I am sure that this query worked at one time, but now I am getting the error message.

Does anyone have an idea how I can get around this problem?
INSERT INTO tblTempPSPHeader ( PSPReferralId, CheckAmount, CheckDate )
SELECT tblTempPSPDetail.PSPReferralId, Sum(tblTempPSPDetail.AmountToPay) AS SumOfAmountToPay, Date() AS Expr1
FROM tblTempPSPDetail
GROUP BY tblTempPSPDetail.PSPReferralId, Date();

Open in new window

LVL 3
dwcummingsAsked:
Who is Participating?
 
mbizupCommented:
The error is being caused by the Date() function in your Group By clause.

It also occurs if you use constants in your Group By clause.

Try removing Date() from your Group By clause, but leave it in the SELECT:

INSERT INTO tblTempPSPHeader ( PSPReferralId, CheckAmount, CheckDate )
SELECT tblTempPSPDetail.PSPReferralId, Sum(tblTempPSPDetail.AmountToPay) AS SumOfAmountToPay, Date() AS Expr1
FROM tblTempPSPDetail
GROUP BY tblTempPSPDetail.PSPReferralId

Open in new window



As an aside, is Date (today's date) what you mean to insert, or are you trying to refer to a column named Date?

If you want to use a column named Date, try this:

INSERT INTO tblTempPSPHeader ( PSPReferralId, CheckAmount, CheckDate )
SELECT tblTempPSPDetail.PSPReferralId, Sum(tblTempPSPDetail.AmountToPay) AS SumOfAmountToPay, [Date]
FROM tblTempPSPDetail
GROUP BY tblTempPSPDetail.PSPReferralId, [Date]

Open in new window



0
 
Jeffrey CoachmanMIS LiasonCommented:
Does this run on it's own and return the correct daytaset:

SELECT tblTempPSPDetail.PSPReferralId, Sum(tblTempPSPDetail.AmountToPay) AS SumOfAmountToPay, Date() AS Expr1
FROM tblTempPSPDetail
GROUP BY tblTempPSPDetail.PSPReferralId, Date();
0
 
dwcummingsAuthor Commented:
Thanks for the help! Worked kike a charm. It was today's date that I wanted to insert into the CheckDate field.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
mbizupCommented:
Glad to help out!

You mentioned that this had worked for you in the past.  Just curious - Have you made a switch from Access to SQL for your back end?

To my knowledge, your syntax would have worked for you previously if you had been using an Access back end.
0
 
dwcummingsAuthor Commented:
Originally the temporary tables were local. But, due to other factors, I had to use a SQL database instead. That is when the problem developed.

Thanks again.
0
 
dwcummingsAuthor Commented:
I actually resolved the issue on my own, but not quite as elegantly as you. I figured it was the Date() expression and I simply deleted it from the query and added the date to the records via an update query. I like your solution better as it saved me a step.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.