asked on

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

Hello,
I need help understanding why the following code fails with an error message of
Msg 164, Level 15, State 1, Procedure spDistributionTableSupportTeamInsert, Line 6
Each GROUP BY expression must contain at least one column that is not an outer reference.
I did fix it after doing some research by removing @TASKORDER from the group by clause - so it is now working fine. However, I'd like to learn why its inclusion in the group by clause caused the code to fail.

CREATE PROCEDURE spDistributionTableSupportTeamInsert
@TASKORDER  nvarchar(50)
AS

INSERT INTO Distribution
(TaskOrder,Name, LName, OBS, Department, [Function], InitialPostedDate, LastPostedDate, 
 EligibilityStatus, [Description], Contribution, DistributionAmt)

SELECT   @TASKORDER  , [Name], [LName], [OBS], [Department], [Function], Min([MinOfInitialPostedDate]), 
		 Max([MaxOfLastPostedDate]), [EligibilityStatus], [Description],  [Contribution], 
		 [DistributionAmt] FROM vwRolledUpSupportTeam WHERE [TaskOrder] <> @TASKORDER		 
		 AND [Name] NOT IN (SELECT [Name] FROM vwShareAllocation WHERE [TaskOrder] = @TASKORDER)
         GROUP BY @TASKORDER, [Name], [LName], [OBS], [Department], [Function], [EligibilityStatus], 
         [Description],  [Contribution], [DistributionAmt]

Open in new window

Shaun Kline

Try giving your @TASKORDER field in the SELECT clause a name, and then use that name in place of @TASKORDER in the GROUP BY. My guess is that the GROUP BY does not like variables.

HainKurt

try this:

ie, no need to add parameter to group since it is fixed...

SELECT   @TASKORDER  , [Name], [LName], [OBS], [Department], [Function], Min([MinOfInitialPostedDate]), 
		 Max([MaxOfLastPostedDate]), [EligibilityStatus], [Description],  [Contribution], 
		 [DistributionAmt] FROM vwRolledUpSupportTeam WHERE [TaskOrder] <> @TASKORDER		 
		 AND [Name] NOT IN (SELECT [Name] FROM vwShareAllocation WHERE [TaskOrder] = @TASKORDER)
         GROUP BY [Name], [LName], [OBS], [Department], [Function], [EligibilityStatus], 
         [Description],  [Contribution], [DistributionAmt]

Open in new window

Juan Velasquez

ASKER

HainKurt

I did what you suggested before I posted the question and it worked. I wanted to know the reason why it worked.

Shaun_Kline:

I agree that the group by doesn't like variables. I'm trying to understand why

ASKER CERTIFIED SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial