May 29, 2014 at 9:13 pm
I am running the below query in Report Builder and it is giving an error saying "An item with the same key has already been added."
This scripts all good in SSMS 2008 and also in Report Builder inside Design a Query window, when I hit '!' it runs and gives me correct output but, when I hit next it gives me this error.
If table structure and example required I can provide that.
SELECT SUB1.PortfolioName, SUB1.SiteName,SUB1.BuildingName,SUB1.AccountTypeName, SUB2.Month,SUB2.Year,SUB2.UsageAmount,SUB1.Month,SUB1.Year,SUB1.UsageAmount
FROM
(SELECT PO.PortfolioName, SI.SiteName, BU.BuildingName,AT.AccountTypeName, AM.Month, AM.Year,AM.InvoiceAmount,AM.UsageAmount,CL.ClientId
FROM AccountMonthly AM
INNER JOIN Client CL ON AM.ClientId = CL.ClientId
INNER JOIN Portfolio PO on CL.ClientId = PO.ClientId
INNER JOIN PortfolioSite PS on PO.PortfolioID = PS.PortfolioID
INNER JOIN Site SI on PS.SiteId = SI.SiteID
INNER JOIN Building BU on AM.BuildingId =BU.BuildingId
INNER JOIN AccountType AT ON AM.AccountTypeId = AT.AccountTypeId
WHERE CL.ClientName LIKE 'ANZ%'
AND PO.PortfolioName = 'AUSTRALIA'
AND SI.SiteName = 'COMMERCIAL'
AND BU.BuildingName LIKE '833 Collins St%'
--AND AM.Year ='2012'
AND AT.AccountTypeName = 'ELECTRICITY')SUB1
INNER JOIN
(SELECT PO.PortfolioName, SI.SiteName, BU.BuildingName,AT.AccountTypeName, AM.Month, AM.Year,AM.InvoiceAmount,AM.UsageAmount,CL.ClientId
FROM AccountMonthly AM
INNER JOIN Client CL ON AM.ClientId = CL.ClientId
INNER JOIN Portfolio PO on CL.ClientId = PO.ClientId
INNER JOIN PortfolioSite PS on PO.PortfolioID = PS.PortfolioID
INNER JOIN Site SI on PS.SiteId = SI.SiteID
INNER JOIN Building BU on AM.BuildingId =BU.BuildingId
INNER JOIN AccountType AT ON AM.AccountTypeId = AT.AccountTypeId
WHERE CL.ClientName LIKE 'ANZ%'
AND PO.PortfolioName = 'AUSTRALIA'
AND SI.SiteName = 'COMMERCIAL'
AND BU.BuildingName LIKE '833 Collins St%'
--AND AM.Year ='2013'
AND AT.AccountTypeName = 'ELECTRICITY')SUB2
ON SUB1.ClientId = SUB2.ClientId
WHERE SUB1.Month = SUB2.Month
AND SUB2.Year = SUB1.Year + 1
AND SUB2.UsageAmount > SUB1.UsageAmount
--ORDER BY SUB1.BuildingName
May 30, 2014 at 1:54 pm
your output columns are named the same, that's the issue. Sub1.month is the same as Sub2.month, etc. Give them aliases, you should be fine.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
June 1, 2014 at 6:08 pm
I had to assign alias to each columns within one of the subquery and not outside the subquery. But,thank you for giving me the right direction.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply