November 15, 2011 at 1:12 pm
Hi, sp is not working in 2008,but is working in 2000 get an error:
Each GROUP BY expression must contain at least one column that is not an outer reference.
@CSM_ID int,
@Sheet_Year int,
@Sheet_Month int
AS
SELECT dbo.ART_Consortiums.Csm_NAME,
dbo.ART_Countries.Country_ID, dbo.ART_Countries.Country_Name,
dbo.ART_CostCategories.CostCategory_Name, dbo.ART_CostCategories.CostCategory_NUM,
dbo.ART_CostCategories.CostCategory_Type,
dbo.ART_Exp_Sheets.Sheet_Year, dbo.ART_Exp_Sheets.Sheet_Month, dbo.ART_Consortiums.Csm_ID,
SUM(dbo.ART_Exp_Records.Exp_Csm_Value) AS CountrySum,
(SELECT SUM(dbo.ART_Exp_Records.Exp_Csm_Value)
FROM dbo.ART_Exp_Records INNER JOIN
dbo.ART_Exp_Sheets ON dbo.ART_Exp_Records.Exp_Sheet_ID = dbo.ART_Exp_Sheets.Exp_Sheet_ID
GROUP BY dbo.ART_Consortiums.Csm_ID, dbo.ART_Exp_Sheets.Sheet_Year,
dbo.ART_Exp_Sheets.Sheet_Month, dbo.ART_Exp_Sheets.Sheet_Type
HAVING
(dbo.ART_Exp_Sheets.Sheet_Year = @Sheet_Year) AND
(dbo.ART_Exp_Sheets.Sheet_Month = @Sheet_Month) AND
(dbo.ART_Consortiums.Csm_ID = @CSM_ID) AND
(dbo.ART_Exp_Sheets.Sheet_Type LIKE 'POS')
) AS ReportTotal
FROM dbo.ART_Exp_Records INNER JOIN
dbo.ART_Exp_Sheets ON dbo.ART_Exp_Records.Exp_Sheet_ID = dbo.ART_Exp_Sheets.Exp_Sheet_ID INNER JOIN
dbo.ART_Countries ON dbo.ART_Exp_Sheets.Country_ID = dbo.ART_Countries.Country_ID INNER JOIN
dbo.ART_Consortiums ON dbo.ART_Countries.Csm_ID = dbo.ART_Consortiums.Csm_ID INNER JOIN
dbo.ART_CostCategories ON dbo.ART_Exp_Records.CostCategory_ID = dbo.ART_CostCategories.CostCategory_ID
GROUP BY dbo.ART_Consortiums.Csm_NAME, dbo.ART_Countries.Country_Name, dbo.ART_Countries.Country_ID, dbo.ART_CostCategories.CostCategory_Name,
dbo.ART_CostCategories.CostCategory_NUM, dbo.ART_CostCategories.CostCategory_Type, dbo.ART_Exp_Sheets.Sheet_Year, dbo.ART_Exp_Sheets.Sheet_Month,
dbo.ART_Consortiums.Csm_ID,dbo.ART_Exp_Sheets.Sheet_Type
HAVING (dbo.ART_Exp_Sheets.Sheet_Year = @Sheet_Year) AND
(dbo.ART_Exp_Sheets.Sheet_Month = @Sheet_Month) AND
(dbo.ART_Consortiums.Csm_ID = @CSM_ID)AND
(dbo.ART_Exp_Sheets.Sheet_Type LIKE 'POS')
ORDER BY dbo.ART_CostCategories.CostCategory_NUM, dbo.ART_CostCategories.CostCategory_Name, dbo.ART_Countries.Country_Name
Can some one please help me.
Thank you
November 15, 2011 at 1:47 pm
What is the error that you are getting?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 15, 2011 at 1:59 pm
Each GROUP BY expression must contain at least one column that is not an outer reference.
November 15, 2011 at 2:02 pm
Krasavita (11/15/2011)
Each GROUP BY expression must contain at least one column that is not an outer reference.
I see that now in your original post - got lost in there
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 15, 2011 at 2:11 pm
How can I fix it? why doesn't work in 2008?
November 15, 2011 at 2:33 pm
In your subselect, you are only referencing tables from outside the subselect in your group by clause.
(SELECT SUM(dbo.ART_Exp_Records.Exp_Csm_Value)
FROM dbo.ART_Exp_Records
INNER JOIN dbo.ART_Exp_Sheets
ON dbo.ART_Exp_Records.Exp_Sheet_ID = dbo.ART_Exp_Sheets.Exp_Sheet_ID
GROUP BY dbo.ART_Consortiums.Csm_ID, dbo.ART_Exp_Sheets.Sheet_Year,
dbo.ART_Exp_Sheets.Sheet_Month, dbo.ART_Exp_Sheets.Sheet_Type
HAVING
(dbo.ART_Exp_Sheets.Sheet_Year = @Sheet_Year) AND
(dbo.ART_Exp_Sheets.Sheet_Month = @Sheet_Month) AND
(dbo.ART_Consortiums.Csm_ID = @CSM_ID) AND
(dbo.ART_Exp_Sheets.Sheet_Type LIKE 'POS')
) AS ReportTotal
Do you need to group by the external /outer reference to art_consortiums? Try finding a different value there to group on.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 15, 2011 at 2:38 pm
Without creating the objects this might be a guess but I think this part of your query:
(SELECT SUM(dbo.ART_Exp_Records.Exp_Csm_Value)
FROM dbo.ART_Exp_Records INNER JOIN
dbo.ART_Exp_Sheets ON dbo.ART_Exp_Records.Exp_Sheet_ID = dbo.ART_Exp_Sheets.Exp_Sheet_ID
GROUP BY dbo.ART_Consortiums.Csm_ID, dbo.ART_Exp_Sheets.Sheet_Year,
dbo.ART_Exp_Sheets.Sheet_Month, dbo.ART_Exp_Sheets.Sheet_Type
HAVING
(dbo.ART_Exp_Sheets.Sheet_Year = @Sheet_Year) AND
(dbo.ART_Exp_Sheets.Sheet_Month = @Sheet_Month) AND
(dbo.ART_Consortiums.Csm_ID = @CSM_ID) AND
(dbo.ART_Exp_Sheets.Sheet_Type LIKE 'POS')
) AS ReportTotal
Needs to have at least one column from the GROUP BY be in the SELECT list instead of only grouping on "outer" columns.
November 15, 2011 at 2:40 pm
Also, I would alias the tables internal to the subquery and use those aliases when referencing the columns.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply