Stored Procedure MS SQL Server 2008 field invalid because it is not contained in agg fuction or in group by

The error below describes the problem I am havin in the code. I am wondering if this could be due to Links to the tables or that I have to identify the table names uniquely....Any help is greatly appreciated


Msg 8120, Level 16, State 1, Procedure Rpt_SalesSummaryAdopted_sproc_RB, Line 75
Column 'IMITMIDX_SQL.prod_cat' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Procedure Rpt_SalesSummaryAdopted_sproc_RB, Line 100
Column 'IMITMIDX_SQL.prod_cat' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Procedure Rpt_SalesSummaryAdopted_sproc_RB, Line 125
Column 'IMITMIDX_SQL.prod_cat' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Procedure Rpt_SalesSummaryAdopted_sproc_RB, Line 147
Column 'IMITMIDX_SQL.prod_cat' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 107, Level 16, State 2, Procedure Rpt_SalesSummaryAdopted_sproc_RB, Line 172
The column prefix 'PD' does not match with a table name or alias name used in the query.
StoredProcwithError.txt
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>Column 'IMITMIDX_SQL.prod_cat' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Eyeballeth thy GROUP BY clause.  Every column in the SELECT clause that does not participate in an aggregate such as Sum(), Count(), Min(), etc. must be in the GROUP BY clause, and it appears that in all statements it's not there.
0
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
Since I am making it part of the Group in the report we are designing the SP by. In Crystal Reports  I might add.

I tried putting in the following after every set

GROUP BY ARCUSFIL_SQL.Terr, OELINHST_SQL.Item_no, IMITMIDX_SQL.prod_cat

what am I doing wrong...Here is new error. One thing I get confused with (new to this obviously) is the error lines clearly help but are their any tricks to identifying the tru line of origin where the problem is.........

Msg 107, Level 16, State 2, Procedure Rpt_SalesSummaryAdopted_sproc_RB, Line 172
The column prefix 'PD' does not match with a table name or alias name used in the query.
Msg 107, Level 16, State 2, Procedure Rpt_SalesSummaryAdopted_sproc_RB, Line 172
The column prefix 'PD' does not match with a table name or alias name used in the query.
Msg 107, Level 16, State 2, Procedure Rpt_SalesSummaryAdopted_sproc_RB, Line 172
The column prefix 'PD' does not match with a table name or alias name used in the query.
StoredProcwithError1.txt
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>The column prefix 'PD' does not match with a table name or alias name used in the query.
In the below code the INNER JOIN that defines PD in line 23 is commented out, so SQL can't identify what any PD.ColumnName's are in lines 18 and 33.
SELECT 

	SS.PerType,
	@CurrWeekEndDate as CurrWeekEndDate, 
	@CurrToDate as CurrToDate, 
	@CurrToDateYTD as CurrToDateYTD,
	@CurrentWeek as CurrentWeek,
	@NumberOfWeeks as NumberOfWeeks,
	@CurrToDate_Period as CurrToDate_Period,
	@CurrToDateYTD_FiscalYear as CurrToDateYTD_FiscalYear,
	Rpt_Territory.Terr_name, 
	Rpt_Territory.Report_Sort, 
	SS.Terr,
	SS.Item_no, 
	Rpt_TerritoryAdopted.Terr_id AS TerrAdopted,
	sum(isnull(SS.[Gross_Sales],0))  AS Gross_Sales,
	sum(isnull(SS.[Cost],0))  AS Cost,
	PD.PRD_A_GLTYPE
	--,sum(isnull(SS.[Prior_Gross_Sales],0))  AS Prior_Gross_Sales,sum(isnull(SS.[Prior_Cost],0))  AS Prior_Cost
FROM #SalesSummary SS
	INNER JOIN Rpt_Territory ON Rpt_Territory.Terr_id = SS.Terr
	LEFT JOIN Rpt_Territory AS Rpt_TerritoryAdopted ON SS.item_no BETWEEN Rpt_TerritoryAdopted.Start_Item_no AND Rpt_TerritoryAdopted.End_Item_no
	--INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_PRODUCTS AS PD 
			--ON PD.PRD_PRODUCT = OD.OD_PRODUCT  -- Added RB

WHERE Rpt_Territory.ActiveFlag = 'Y' 
GROUP BY SS.PerType,
	 Rpt_Territory.Terr_name, 
	 Rpt_Territory.Report_Sort, 
	 SS.Terr,
	 SS.Item_no,
   	 Rpt_TerritoryAdopted.Terr_id,
   	 PD.PRD_A_GLTYPE
 

Open in new window

0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
One more time I got incorrect line Syntax....My problem is I am coming in behind someone else to fix the code I am not new all together to SPs but I am green. I do not understand all the basics obviously. I am used to writing simple efficient stored procedures for performance. These bound multible tables together to complete one temp table for reporting purposes.

The problem now is the portion at the bottom. Where it says FROM #SalesSummary SS
I do not know what the guy was doing when he said this :

FROM #SalesSummary SS
            CHLROS05.RAMSDB.dbo.ROSS_SALESHDR AS OH
      INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_CUSTOMER AS CM  -- Added RB
                  ON OH.OH_CUSTOMER_NUMBER = CM.CM_CUSTKEY   -- Added RB
                  AND CM.CM_ACCT_GLPOSTS = '000004'
                  AND left(CM.CM_SELLZONE,1) = '4'
                  AND CM.CM_CUST_SUB_TYPE <> 3
      INNER JOIN  CHLROS05.RAMSDB.dbo.ROSS_SALESDTL AS OD         -- Added RB
                  ON OD.OD_TICKET_NUMBER = OH.OH_TICKET_NUMBER         -- Added RB
      INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_PRODUCTS AS PD         -- Added RB
                  ON PD.PRD_PRODUCT = OD.OD_PRODUCT  -- Added RB
      INNER JOIN Rpt_Territory ON Rpt_Territory.Terr_id = SS.Terr
      LEFT JOIN Rpt_Territory AS Rpt_TerritoryAdopted ON SS.item_no BETWEEN Rpt_TerritoryAdopted.Start_Item_no AND Rpt_TerritoryAdopted.End_Item_no


All I know is I need to add some of the tables listed so I can get a more complete set of data.
StoredProcwithError2.txt
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Not sure what your question is.

Any table with a # prefix is a temporary table, meaning it's created on the fly within the SP with a 'CREATE TABLE #SalesSummary' block, and is only used within the SP.  Most of the time, the SP will have many statements that affect the #table, and then the SP will return the #table with a 'SELECT * FROM #table' line at the bottom.  

This is one of SQL Server stored procedures' greatest strengths.

As far as what the original developer was thinking when s/he built it?  No idea, other than what can be read from code comments.   Mind reading is not a great strength of most developers.

>All I know is I need to add some of the tables listed
Permanent tables.  Might be another script/process that creates them.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
btw I think we've nailed the original question.

If you have more questions as you discover them, especially if it's a 'help me understand this block of code' type question, it would be better to ask it as a new question.  Mostly because experts will not jump into a question already nailed, but also because single experts aren't always around 24/7.

Otherwise, more than happy to help.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RUA Volunteer2?Tableau Trainer & Consultant Sales Exec.Author Commented:
Thanks that ot me out of the hole I was stuck in.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.