CODEWITHSUNDEEP
sqlreporting-services
JJK
JJK

Reputation: 13

An item with the same key has already been added. SSRS Report Builder

i know that there are already some forums on here, but can someone help me identify the duplicates and how to alias the column names? I believe the left joins at the bottom are creating this error in report builder. 

SELECT  CASE A.DTASERIES
      WHEN 2 THEN 'Financial'
      WHEN 3 THEN 'Sales'
      WHEN 4 THEN 'Purchasing'
      WHEN 5 THEN 'Inventory'
      WHEN 6 THEN 'Payroll – USA'
      WHEN 7 THEN 'Project'
      ELSE ''
    END AS DTA_Series ,
    JRNENTRY AS JournalEntry,
    D.ACTNUMST AS AccountNumber,
    C.ACTDESCR AS AccountDescription,
    A.GROUPID AS DTA_GroupID,
    CODEID AS DTA_CodeID,
    GROUPAMT ,
    CASE 
    WHEN CODEAMT > 0 THEN CODEAMT
    ELSE 0 
    END AS Debit,
    CASE 
    WHEN CODEAMT < 0 THEN CODEAMT
    ELSE 0 
    END AS Credit,
    A.TRXDATE  AS TransactionDate,
    A.DTAREF AS DTA_Reference,

    DTA_GL_Reference ,
    A.DOCNUMBR AS OriginalDocumentNumber ,
    A.RMDTYPAL ,
    CASE PSTGSTUS
      WHEN 1 THEN 'Unposted'
      WHEN 2 THEN 'Posted'
      ELSE ''
    END AS DTA_PostingStatus ,
    B.DOCNUMBR ,
    B.RMDTYPAL ,
    POSTDESC AS PostingDescription,
    DTAQNTY 
    FROM    dbo.DTA10100 AS A
    LEFT OUTER JOIN dbo.DTA10200 AS B ON A.ACTINDX = B.ACTINDX
                                         AND A.DOCNUMBR = B.DOCNUMBR
                                         AND A.DTAREF = B.DTAREF
                                         AND A.DTASERIES = B.DTASERIES
                                         AND A.GROUPID = B.GROUPID
                                         AND A.SEQNUMBR = B.SEQNUMBR
    LEFT OUTER JOIN dbo.GL00100 AS C ON A.ACTINDX = C.ACTINDX
    LEFT OUTER JOIN dbo.GL00105 AS D ON C.ACTINDX = D.ACTINDX

Upvotes: 1

Views: 10475

Answers (2)

Ztarded
Ztarded

Reputation: 21

Just in case it helps anyone else:

I had this same error in SSRS 2008, SQL Server 2008 R2. My stored procedure was doing a SELECT * FROM #Results to return the result set. There were no duplicate column names/alias's. However, when I switched it to just select all of the columns individually instead of the *, it fixed the issue.

Upvotes: 2

stubaker
stubaker

Reputation: 1958

SSRS only looks at column names while determining fields for your dataset, and throws this error if you SELECT two columns with the same name from one or more tables.

In this case you have A.RMDTYPAL and B.RMDTYPAL in your SELECT clause, which SSRS sees as two RMDTYPAL columns. Alias one or both of these and this error should go away.

See this similar question for a little more information.

EDIT: (for clarity I'm only listing the two columns)

SELECT  
    A.RMDTYPAL AS A_RMDTYPAL,
    B.RMDTYPAL AS B_RMDTYPAL
FROM dbo.DTA10100 AS A
    LEFT OUTER JOIN dbo.DTA10200 AS B ON A.ACTINDX = B.ACTINDX
                                     AND A.DOCNUMBR = B.DOCNUMBR
                                     AND A.DTAREF = B.DTAREF
                                     AND A.DTASERIES = B.DTASERIES
                                     AND A.GROUPID = B.GROUPID
                                     AND A.SEQNUMBR = B.SEQNUMBR
    LEFT OUTER JOIN dbo.GL00100 AS C ON A.ACTINDX = C.ACTINDX
    LEFT OUTER JOIN dbo.GL00105 AS D ON C.ACTINDX = D.ACTINDX

Upvotes: 2

Related Questions