Each GROUP BY error message when there is no literal?

Question

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: 95460 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: 11845 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

on y.Subscriber_ID = x.Subscriber_ID

You're grouping by fields in tables a and b, but those tables are not referenced there.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

BIMind SSChasing Mays Points: 628 More actions · Answer 3

Thanks for the tip!

Btw...the code is formatted...i am still new around here...i guess i need to learn how to enclose code so its formatted in here 🙂

SELECT col1 FROM tbl1

--test

BIMind SSChasing Mays Points: 628 More actions · Answer 4

BIMind

SSChasing Mays

Points: 628

April 20, 2010 at 2:05 pm

#1154923

oh that was easy 🙂