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

Question

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

Please try to change `LEFT OUTER JOIN dbo.GL00105 AS D ON C.ACTINDX = D.ACTINDX` into `LEFT OUTER JOIN dbo.GL00105 AS D ON A.ACTINDX = D.ACTINDX` it seems to be the same but connection should be clearer — , Dec 19 '14 at 19:53
Could you tell whether each of the columns that **doesn't** have table alias prefix (i.e. `JRNENTRY`) is for sure unique in column set of all joined tables? — , Dec 19 '14 at 19:57
In fact, I would just put a table alias on every column. It makes it easier for future analysis & debugging. — Tab Alleman, Dec 19 '14 at 20:03

score 2 · Answer 1 · edited May 23 '17 at 10:31

2

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

edited May 23 '17 at 10:31

Community

1
1

answered Dec 19 '14 at 23:06

stubaker

1,928
1
23
25

Thanks for the reply- could you give me an example of creating an alias for one of the columns. Taking A.GROUPID for example, it is aliased at the top of the statement as DTA_GroupID and when i change the A.GROUPID in the LEFT OUTER JOIN section (bottom of the query) SQL does not take it. So when i have A.DTA_GroupID = B.GROUPID the query has errors. Trying to figure out how i'm aliasing this wrong. – JJK Dec 20 '14 at 00:23
@JJK See edit above for an example. In SQL the [order of operations](http://www.bennadel.com/blog/70-sql-query-order-of-operations.htm) are `FROM` clause then `SELECT` clause, so you can't use column aliases in your join conditions. – stubaker Dec 20 '14 at 00:57
@stubaker if the tables contain many columns, then how to avoid listing all columns ? it's hard for maintenance in future. – Grace Aug 17 '22 at 12:12

score 2 · Answer 2 · answered Mar 24 '16 at 17:09

2

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.

answered Mar 24 '16 at 17:09

Ztarded

21
1

if the tables contain many columns, then how to avoid listing all columns ? it's hard for maintenance in future. – Grace Aug 17 '22 at 12:10

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

2 Answers2