[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1877
  • Last Modified:

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

0
dwcummings
Asked:
dwcummings
  • 3
  • 2
1 Solution
 
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 CoachmanCommented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now