Each GROUP BY expression must contain at least one

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 2005 Forums

Transact-SQL (2005)

Each GROUP BY expression must contain at least one

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2008-11-10 : 08:01:53

i have moved a DB from sql2000 to sql2005.
i got one of the Stored Procedures this error :

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

i recive this on a line where the code is :


exec sp_executesql @str,N'@xmldoc int',@xmldoc

and the @str is an sql :


select distinct 
cmy,App1,TVE,RTrim(Substring(Cast(vw_pb.SDate as nvarchar(14)),1,8)),count(*) from vw_pb where
  SDate between 20081109000000 And 20081109235959 and lower(LE1) in ('xx','mail') 
 and cmy=N'mycmy1234' 
group by  cmy,App1,TVE,RTrim(Substring(Cast(vw_pb.SDate as nvarchar(14)),1,8))

and the @xmldoc hold a value of an opened xml document

what can cause the error i recive?

thnaks in advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-10 : 08:21:57

This error comes when you use columns from an outer view in group by clause of the inner view. It seems to work fine in sql 2k though. Not sure why you're getting this problem, Unless vw_pub is a view, Is it so ?? If yes, Can you post the code for it as well please.

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2008-11-10 : 09:59:32

yes its a view and it look's like this :


SELECT     *
FROM  Ms2007September (nolock)
UNION ALL
SELECT     *
FROM  Ms2007october (nolock)
.....................

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-10 : 10:04:18

and Ms2007September , Ms2007october ??? Are these views too ?

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2008-11-10 : 10:08:54

no this are tables

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2008-11-10 : 10:35:51

what do u mean by

quote:
outer view

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources