Solved

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

Posted on 2011-03-14
4
1,449 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:chtullu135
  • 2
4 Comments
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 35131362
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.
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 35131690
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

0
 

Author Comment

by:chtullu135
ID: 35138366
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
0
 
LVL 51

Accepted Solution

by:
HainKurt earned 500 total points
ID: 35138619
You can group by only fields or expression containing some column
and there is no reason adding some fixed value to grouping, just it does not make any sense... just add that value to select part...
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Get Duration of last Status Update 4 34
SQL Server Question 5 36
Generate Weekly Schedule 15 28
SQL Improvement  ( Speed) 14 26
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now