Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Each GROUP BY expression must contain at least one column that is not an outer reference. Expand / Collapse
Author
Message
Posted Monday, November 10, 2008 5:02 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:11 AM
Points: 317, Visits: 929
Hi,


i'm raising the summary tables by using the following query,


INSERT INTO ClientTbl_SummarySalesMonthTY
([RetailerKey]
,[ProductLevel1]
,[AttributeValue]
,[Month]
,[Year]
,[SalesinUnits]
,[GrossSalesValueinDollars]
,[NetSaleValue]
,[StockOnHandinUnits]
,[StockOnHandinDollars]
,[StockOnOrderinUnits]
,[StockOnOrderinDollars]
,[ReplenishmentType]
,[ProductStatus])
select p.Retailerkey
,'Category'
,p.Category
,c.MonthNumRetailYear
,c.RetailYear
,sum(f.[Quantity Sold])
,sum(f.[Gross Sales Value Incl. Tax])
,sum(f.[Net Sales Value Incl. Tax])
,sum(f.[Store Stock Units on Hand])
,sum(f.[Store Stock On Hand Value Excl. Tax])
,sum(f.[Store Stock Units on Order])
,sum(f.[Store Stock On Order Value Excl. Tax])
,p.ReplenishmentType
,P.ProductStatus

from ClientAdmin.dbo.ClientTbl_DimProduct p
inner join ClientAdmin.dbo.ClientTbl_DimCalendar c
on p.retailerkey = c.retailerkey
inner join ClientAdmin.dbo.ClientTbl_FactSalesStock f
on p.productkey =f.productkey and p.retailerkey =f.retailerkey and c.calendarkey=f.calendarkey

group by p.Retailerkey
,'Category'
,p.Category
,c.MonthNumRetailYear
,c.RetailYear
,p.ReplenishmentType
,P.ProductStatus

and getting the following error

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

Early help is highly appreciated.

Thanks
Regards
Viji
Post #599762
Posted Monday, November 10, 2008 5:10 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 6:13 AM
Points: 1,694, Visits: 19,550

Remove 'Category' from your GROUP BY clause


____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #599768
Posted Monday, November 10, 2008 5:36 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:11 AM
Points: 317, Visits: 929
gr8 it works

thanks a lot Mr. Mark
Viji
Post #599780
Posted Tuesday, December 13, 2011 9:45 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, February 7, 2012 10:14 AM
Points: 27, Visits: 30
Good info about this topic is discussed at:

http://www.a2zmenu.com/Blogs/SQL/Each-GROUP-BY-expression-must-contain-at-least-one-column-that-is-not-an-outer-reference.aspx
Post #1221012
Posted Tuesday, December 13, 2011 10:06 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 8:00 AM
Points: 2,433, Visits: 7,501
sandeep.cs3 (12/13/2011)
Good info about this topic is discussed at:

SNIP


This thread is from 2008. . .



Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1221023
Posted Saturday, April 21, 2012 12:09 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 3:59 PM
Points: 1, Visits: 43
and yet that link was still helpful to me today, in 2012 :)
Post #1287661
Posted Thursday, April 3, 2014 12:28 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 9:13 AM
Points: 80, Visits: 341
..and that link is no longer valid, in 2014
Post #1558165
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse