0

I am trying to run reporting services with below SQL query

select ca.callingpartynumber, ca.originalcalledpartynumber, case
when calledpartylastname is not null then ca.calledpartylastname + ',' + calledpartyfirstname 
else p1.name end,
p1.location, p1.dept, p1.title,
case
when callingpartylastname is not null then ca.callingpartylastname + ',' + callingpartyfirstname 
else p3.name end
from calldata.calldetailreport ca
join ps_bc_peoplesource_base p1 on ca.originalcalledpartynumber = replace(p1.bc_int_phone, '-', '')
left outer join ps_bc_peoplesource_base p3 on ca.callingpartynumber = replace(p1.bc_int_phone, '-', '')
where originalcalledpartynumber in (select replace(bc_int_phone, '-', '') internal_modified  from ps_bc_peoplesource_base where bc_lan_id = 'f7c')
--and datetimedisconnect between @startdate  and @enddate --1221

I get this error-

“An item with the same key has already been added.”

HaveNoDisplayName
  • 8,291
  • 106
  • 37
  • 47
error_404
  • 25
  • 1
  • 1
  • 6
  • This issue because of there are 2 columns with the same name that you are selecting. Make sure to change the final name & that every column when you do run your statement in Management Studio has a unique name. If two column in two different tables, and you are added them in Select Clause, add ALIAS name for each coilumn – HaveNoDisplayName Jul 21 '15 at 18:35
  • 2
    Have you tried providing an explicit alias to your 2 `case ... end` statements? `case ... end as `. – sstan Jul 21 '15 at 18:36
  • Thanks! However Now I get an error which says- "the value expression for text box 'finalcalledpartynumber' refers to the field 'finalcalledpartynumber'. Report items can only refer to the current database scope....' What does this mean? Again thanks so much! – error_404 Jul 21 '15 at 20:51

1 Answers1

1

You are missing Column Alias for Two Case Statement in Your SELECT query. As SSRS uses only the column name as the key, not table + column, so it was choking.

Refer Here And Here And Here also

SELECT ca.callingpartynumber, ca.originalcalledpartynumber, 
    CASE WHEN calledpartylastname IS NOT NULL  
            THEN ca.calledpartylastname + ',' + calledpartyfirstname 
         ELSE p1.name END AS calledpartylastname,
    p1.location, 
    p1.dept, 
    p1.title,
    CASE WHEN callingpartylastname IS NOT NULL 
            THEN ca.callingpartylastname + ',' + callingpartyfirstname 
        ELSE p3.name END AS callingpartylastname
    ...
    ...
Community
  • 1
  • 1
HaveNoDisplayName
  • 8,291
  • 106
  • 37
  • 47
  • Thanks! However Now I get an error which says- "the value expression for text box 'finalcalledpartynumber' refers to the field 'finalcalledpartynumber'. Report items can only refer to the current database scope....' What does this mean? Again thanks so much! – error_404 Jul 21 '15 at 20:48
  • This error because you are referring to this 'finalcalledpartynumber' but this column does not exist in current scope/dataset/region. This field is not in your above stated queryso it means you have somehow another query also – HaveNoDisplayName Jul 21 '15 at 21:46