Urgent help needed please ... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Urgent help needed please ...

Author

Topic

swenri
Yak Posting Veteran

72 Posts

Posted - 2007-08-23 : 13:41:47

I'm getting an error in Visual Studio 2005,when I run this query. It says Group by error. I'm using this to create a report The same query works in management studio in sql server 2005
Tried all methods .. no success. Also is there any editor where we can debug a query in VS 2005, like in management studio.

Please help. Thanks,
------------------------------------------------------------------
SELECT DISTINCT
'0' AS Cpgn_Tot_MailedQty,
ISNULL(GIFTS.Final_No_Of_Gifts, 0) AS Final_No_of_Gifts,
ISNULL(GIFTS.Final_Total_Income, 0) AS Final_Total_Income,
ISNULL(Campaign_One.Final_Mailing_Cost, 0) AS Final_Mailing_Cost, ISNULL(Campaign_One.Final_Fulfillment_Cost, 0) AS Final_Fulfillment_Cost,
ISNULL(Campaign_One.CampaignId, 0) AS CampaignID
FROM (SELECT CampaignId, SUM(ISNULL(MailActualCost, 0)) AS Final_Mailing_Cost, SUM(ISNULL(FulfillmentActualCost, 0)) AS Final_Fulfillment_Cost
FROM PortfolioReport.Campaign
WHERE (CampaignDesc = '1988 Christmas - I')
GROUP BY CampaignId) AS Campaign_One LEFT OUTER JOIN
(SELECT PortfolioReport.Source.CampaignId, COUNT(ISNULL (PortfolioReport.Gift.GiftId, 0)) AS Final_No_Of_Gifts,
SUM(ISNULL(PortfolioReport.Gift.LineItemAmount, 0)) AS Final_Total_Income
FROM PortfolioReport.Gift INNER JOIN
PortfolioReport.Source ON PortfolioReport.Gift.SourceId = PortfolioReport.Source.SourceId INNER JOIN
PortfolioReport.Campaign AS Campaign_1 ON PortfolioReport.Source.CampaignId = Campaign_1.CampaignId
WHERE (PortfolioReport.Source.SourceId <> 0) AND (Campaign_1.CampaignDesc = '1988 Christmas - I') AND
(PortfolioReport.Gift.DateDeposited >=
(SELECT MIN(g.DateDeposited) AS DateDeposited
FROM PortfolioReport.Gift AS g INNER JOIN
PortfolioReport.Source AS s ON g.SourceId = s.SourceId INNER JOIN
PortfolioReport.Campaign AS c ON s.CampaignId = c.CampaignId
WHERE (c.CampaignDesc = '1988 Christmas - I') AND (g.LineItemAmount > 0) AND (g.DateDeposited >=
(SELECT (MIN(c.DateStart) AS DateStart
FROM PortfolioReport.Source AS s INNER JOIN
PortfolioReport.Campaign AS c ON c.CampaignId = s.CampaignId
WHERE (c.CampaignDesc = '1988 Christmas - I') AND (c.DateStart IS NOT NULL))))) AND
(PortfolioReport.Gift.DateDeposited <=
(SELECT MIN(g.DateDeposited) +
(SELECT DATEDIFF(day,
(SELECT MIN(G.DateDeposited) AS DateDeposited
FROM PortfolioReport.Gift AS G INNER JOIN
PortfolioReport.Source AS S ON G.SourceId = S.SourceId INNER JOIN
PortfolioReport.Campaign AS C ON S.CampaignId = C.CampaignId
WHERE (G.LineItemAmount > 0) AND (C.CampaignDesc = '1988 Christmas - I') AND (G.DateDeposited >= (SELECT MIN(C.DateStart) AS DateStart
FROM PortfolioReport.Source AS s INNER JOIN
PortfolioReport.Campaign AS c ON C.CampaignId = s.CampaignId
WHERE (C.CampaignDesc = '1988 Christmas - I') AND (C.DateStart IS NOT NULL)))),
(SELECT MAX(G.DateDeposited) AS DateDeposited
FROM PortfolioReport.Gift AS G INNER JOIN
PortfolioReport.Source AS S ON G.SourceId = S.SourceId INNER JOIN
PortfolioReport.Campaign AS C ON S.CampaignId = C.CampaignId
WHERE (G.LineItemAmount > 0) AND (C.CampaignDesc = '1988 Christmas - I'))) AS 'LFY Last Gift Date')
FROM PortfolioReport.Gift g INNER JOIN
PortfolioReport.Source s ON g.sourceid = s.sourceid INNER JOIN
PortfolioReport.Campaign c ON s.campaignid = c.campaignid
WHERE c.CampaignDesc = '1988 Christmas - I' AND g.LineItemAmount > 0 AND g.DateDeposited >=
(SELECT min(c.DateStart)
FROM PortfolioReport.Source s INNER JOIN
PortfolioReport.Campaign c ON c.Campaignid = S.Campaignid
WHERE c.CampaignDesc = @Comparison_Campaign_Desc AND c.DateStart IS NOT NULL))
GROUP BY PortfolioReport.Source.CampaignId) AS GIFTS ON Campaign_One.CampaignId = GIFTS.CampaignId

X002548
Not Just a Number

15586 Posts

Posted - 2007-08-23 : 13:47:31

You have to group by all columns that are not part of a scalar function

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources