Each GROUP BY error message when there is no literal?

Question

Post reply

Each GROUP BY error message when there is no literal?

BIMind

SSChasing Mays

Points: 628
More actions
April 20, 2010 at 11:40 am

#221555

Hi

Can i have someone look at this and figure out why its giving this error:

Msg 164, Level 15, State 1, Line 11

Each GROUP BY expression must contain at least one column that is not an outer reference.

I did some research and saw this is typically thrown by a literal inserted but i dont have any. BTW this code works error free in 2000. I am porting this to 2008.

update dbo.tempTable

set OL_Key = x.Product_Key

from (select a.Subscriber_ID, b.Product_Key, b.Order_Submit_Date_Key

from dbo.tempTable a

left outer join dbo.SE_Ords b

on b.Subscriber_ID = a.Subscriber_ID

where b.Order_Submit_Date_Key = (SELECT MAX(Order_Submit_Date_Key)

FROM dbo.SE_Ords bb

WHERE b.Order_ID = bb.Order_ID

and bb.Order_Submit_Date_Key < convert(varchar(8),a.post_date,112)

group by a.Subscriber_ID, b.Product_Key, b.Order_Submit_Date_Key))x

left outer join dbo.tempTable y

on y.Subscriber_ID = x.Subscriber_ID

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95405 More actions · Answer 1

The group by clause (in your second nested select) is referencing a column that is in a table not included in the select clause.

You might be able to avoid the group by issue by changing that select to:

select top 1 Order_Submit_Date_Key



  FROM dbo.SE_Ords bb

 WHERE b.Order_ID = bb.Order_ID

   AND bb.Order_Submit_Date_Key < convert(varchar(8),a.post_date,112)

 ORDER BY Order_Submit_Date_Key DESC

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Ben Teraberry SSChampion Points: 11815 More actions · Answer 2

I would highly suggest cleaning up your code. SQL doesn't care if you mash everything together, but for the sake of yourself and others it would be a great benefit.

Like Wayne said, you have an issue with the nested derived table with the group by clause. You do need to change that. This is what you have right now and it should make sense why it doesn't work:

update dbo.tempTable



	set OL_Key = x.Product_Key

	from

		(

		select a.Subscriber_ID,

				b.Product_Key,

				b.Order_Submit_Date_Key

		from dbo.tempTable a

		left outer join dbo.SE_Ords b

			on b.Subscriber_ID = a.Subscriber_ID

		where b.Order_Submit_Date_Key =

			(

			SELECT MAX(Order_Submit_Date_Key)

			FROM dbo.SE_Ords bb

			WHERE b.Order_ID = bb.Order_ID

				and bb.Order_Submit_Date_Key < convert(varchar(8),a.post_date,112)

			group by a.Subscriber_ID, b.Product_Key, b.Order_Submit_Date_Key

			)

		)x

	left outer join dbo.tempTable y