CODEWITHSUNDEEP
sqlvisual-studioreporting-services
Inch High
Inch High

Reputation: 845

Item with the Same Key Already Added (SQL)

I'm having a problem with some SQL I'm writing. Whilst I know the Error means that I have referenced the same column twice I can't seem to find where. It runs fine in SQL Server Management but when I try to put it into Visual Studio to create a report from it, it gives me the error.

Here is my code:

SELECT 
    TC.CorrespondanceName1
    , LC.Address1
    , LC.Address2
    , LC.Address3
    , LC.Address4
    , LC.PostCode
    , TC.Telephone
    , (DATEDIFF(yy,PC.DateofBirth,GetDate()) - (CASE WHEN GetDate() < DATEADD(yy,DATEDIFF(yy,PC.DateofBirth,GetDate()), PC.DateofBirth) THEN 1 ELSE 0 END)) AS Age
    , CASE WHEN PAC.AttribCode = 'CON1' AND PAC.LogValue = 1 THEN 'Yes' ELSE 'No' END AS [Telephone]
    , CASE WHEN PAC.AttribCode = 'CON2' AND PAC.LogValue = 1 THEN 'Yes' ELSE 'No' END AS [Post]
    , CASE WHEN PAC.AttribCode = 'CON3' AND PAC.LogValue = 1 THEN 'Yes' ELSE 'No' END AS [Email]
    , CASE WHEN PAC.AttribCode = 'CON4' AND PAC.LogValue = 1 THEN 'Yes' ELSE 'No' END AS [Text]
    , CASE WHEN PAC.AttribCode = 'CON5' AND PAC.LogValue = 1 THEN 'Yes' ELSE 'No' END AS [Facebook]
    , CASE WHEN PAC.AttribCode = 'CON6' AND PAC.LogValue = 1 THEN 'Yes' ELSE 'No' END AS [Website]
    , CASE WHEN PAC.AttribCode = 'CON7' AND PAC.LogValue = 1 THEN 'Yes' ELSE 'No' END AS [Meeting]
FROM 
    vwTenancyCurrent TC 
INNER JOIN
    vwLocationCurrent LC ON LC.PlaceRef = TC.PlaceRef 
INNER JOIN
    vwTenancyPersonCurrent TPC ON TPC.TenancySystemRef = TC.TenancySystemRef 
INNER JOIN
    vwPersonCurrent PC ON PC.PersonRef = TPC.PersonRef 
INNER JOIN
    vwPersonAttributesCurrent PAC ON PAC.PersonRef = PC.PersonRef 
WHERE 
    TC.TenancyStatus <> 'FOR'
    AND PC.DateofBirth <> ''

I've done some digging and the error definitely relates to my Case statements but I've used the Case statement this way before and never had this problem.

Can anyone help?

Thanks

Upvotes: 1

Views: 86

Answers (2)

Sachu
Sachu

Reputation: 7766

TC.Telephone,
, CASE WHEN PAC.AttribCode = 'CON1' AND PAC.LogValue = 1 
         THEN 'Yes' ELSE 'No' 
  END AS `[Telephone]`

Change second alias as something another name Telephone_home etc.. then your code will work

Upvotes: 2

S&#233;bastien Sevrin
S&#233;bastien Sevrin

Reputation: 5405

The Telephone column appears twice in your query.

SSMS can handle a query with multiple identical column names but you can't use such a query in a Report.
This is by design, column names are used as a key.

Rename 1 of them and the problem will go away.

Upvotes: 3

Related Questions