I need help understanding why the following code fails with an error message of
Msg 164, Level 15, State 1, Procedure spDistributionTableSupport
, 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
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]