8120 error with SQL Server that doesn't make sense

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

8120 error with SQL Server that doesn't make sense

Author

Topic

glennforum
Starting Member

4 Posts

Posted - 2006-12-11 : 10:59:08

Hi,

I am having a problem with a group by. Please review the SQL I have presented below. Any suggestions would be greatly appreciated.

The following SQL executes properly.

DECLARE @IntVar As int

SELECT @IntVar = 965

SELECT POS.IssueNumber,
DATEPART(ww,CAST(POS.SaleDate As DateTime)) AS Week,
-- dbo.svf_GetFirstWeek(@IntVar,POS.IssueNumber,CAST(DATEPART(ww,CAST(POS.SaleDate As DateTime)) As Int)),
SUM(Quantity) As QuantitySold
FROM dbo.PointOfSale POS
WHERE POS.IntVar = @IntVar
GROUP BY POS.IssueNumber,DATEPART(ww,CAST(POS.SaleDate As DateTime))
ORDER BY POS.IssueNumber,DATEPART(ww,CAST(POS.SaleDate As DateTime))

Notice the function call is commented out.

If I uncomment the function call then I get the following error:

Msg 8120, Level 16, State 1, Line 5
Column 'POS.SaleDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Any suggestions? Your help is greatly appreciated.

Regards,
Glenn

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-11 : 11:21:49

Why you need to cast Datepart output to int, isn't it Int by default?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

glennforum
Starting Member

4 Posts

Posted - 2006-12-11 : 11:30:09

I don't need the extra cast you are correct. I was trying everything I could to solve the problem. :)

glennforum
Starting Member

4 Posts

Posted - 2006-12-11 : 11:58:38

I have tried putting that DATEPART as part of a simple subtraction within the select and I get the same error. Seems really strange that I can use that as a stand-alone column but when I tried to put it in as a calculation or function parameter I get the group by error.

I have been developing SQL for many years and have never experienced this before.

glennforum
Starting Member

4 Posts

Posted - 2006-12-11 : 14:07:41

I solved the problem with a temp table but I think this is a bug in the query processor on SQL Server!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources