What is causing 'an item with the same key has already been added' error in ssrs?

Question

I'm trying to run the following query for ssrs reporting services for a data portal in our Prophet 21 database (erp software from Epicor) and I'm running into some errors when trying to input the report in visual basic. I'm getting the following error:

TITLE: Microsoft SQL Server Report Designer

An error occurred while the query design method was being saved. An item with the same key has already been added.

---

BUTTONS:

OK

SELECT
inv_mast.item_desc,
oe_hdr.company_id, 
oe_hdr.order_no, 
oe_hdr.validation_status, 
oe_hdr.order_date, 
oe_hdr.requested_date, 
oe_hdr.address_id, 
oe_hdr.ship2_name, 
oe_hdr.ship2_city, 
oe_hdr.ship2_state, 
oe_hdr_salesrep.salesrep_id, 
p21_view_oe_line.line_no, 
p21_view_oe_line.required_date
, inv_mast.item_id,
 --p21_view_oe_line.qty_ordered, 
p21_view_oe_line.unit_quantity, 
p21_view_oe_line.unit_size, 
p21_view_oe_line.qty_allocated, 
p21_view_oe_line.qty_on_pick_tickets, 
p21_view_oe_line.qty_invoiced, 
p21_view_oe_line.qty_canceled, 
p21_view_oe_line.unit_of_measure, 
p21_view_oe_line.disposition, 
p21_view_oe_line.unit_price, 
CASE p21_view_oe_line.po_cost
WHEN 0 THEN
p21_view_oe_line.sales_cost ELSE
p21_view_oe_line.po_cost
END 
oe_line_sales_cost, 
po_hdr.supplier_id, 
po_hdr.order_date, 
po_line.date_due, 
po_line.qty_ordered, 
po_line.qty_received,
po_line.po_no,
po_hdr.po_class1,oe_hdr.customer_id,
customer.customer_name,
supplier.supplier_name,
oe_hdr.taker,
users.name
,contacts.first_name,
contacts.mi,
contacts.last_name,
p21_view_oe_line.pricing_unit,
p21_view_oe_line.pricing_unit_size,
p21_view_oe_line.qty_staged
, oe_hdr.location_id, location.location_name,
oe_hdr.completed,
oe_hdr.po_no,
customer.currency_id,

CASE WHEN customer.currency_id <> company.home_currency_id THEN
p21_view_oe_line.unit_price_home
END 
unit_price_home,
CASE WHEN customer.currency_id <> company.home_currency_id THEN
CASE p21_view_oe_line.po_cost_home
WHEN 0 THEN
p21_view_oe_line.sales_cost_home 
ELSE
p21_view_oe_line.po_cost_home
END END 
oe_line_sales_cost_home,
company.home_currency_id,
CASE WHEN customer.currency_id <> company.home_currency_id THEN
( p21_view_oe_line.qty_ordered - p21_view_oe_line.qty_invoiced - p21_view_oe_line.qty_canceled - p21_view_oe_line.qty_staged ) * ( p21_view_oe_line.unit_price_home / p21_view_oe_line.pricing_unit_size ) 
END calc_open_price_home,
CASE WHEN customer.currency_id <> company.home_currency_id THEN
'Home : '
END currency_type
, CASE oe_hdr.job_control_flag
WHEN 'Y'
THEN oe_hdr.job_name
ELSE '' 
END job_name,
oe_hdr.order_type,
p21_view_oe_line.user_line_no,
p21_view_prod_order_line_link.prod_order_number
--prod_order_hdr_ud.production_status
FROM oe_hdr
LEFT JOIN p21_view_oe_line ON p21_view_oe_line.order_no = oe_hdr.order_no
left join p21_view_prod_order_line_link ON p21_view_oe_line.oe_line_uid = p21_view_prod_order_line_link.transaction_uid
--left join prod_order_hdr_ud with (NOLOCK) ON p21_view_prod_order_line_link.prod_order_number = prod_order_hdr_ud.prod_order_number
left join prod_order_hdr with (NOLOCK) ON p21_view_prod_order_line_link.prod_order_number = prod_order_hdr.prod_order_number
LEFT JOIN company with (NOLOCK) ON company.company_id = oe_hdr.company_id
LEFT JOIN inv_mast with (NOLOCK) ON inv_mast.inv_mast_uid = p21_view_oe_line.inv_mast_uid
LEFT JOIN oe_hdr_salesrep with (NOLOCK) ON oe_hdr_salesrep.order_number = oe_hdr.order_no      
LEFT JOIN contacts with (NOLOCK) ON contacts.id = oe_hdr_salesrep.salesrep_id LEFT JOIN customer ON customer.customer_id = oe_hdr.customer_id AND
customer.company_id = oe_hdr.company_id
LEFT JOIN users with (NOLOCK) ON users.id = oe_hdr.taker
LEFT JOIN location with (NOLOCK) ON location.location_id = oe_hdr.location_id
LEFT JOIN oe_line_po with (NOLOCK) LEFT JOIN po_hdr with (NOLOCK) ON po_hdr.po_no = oe_line_po.po_no
LEFT JOIN po_line with (NOLOCK) ON po_line.po_no = oe_line_po.po_no AND
po_line.line_no = oe_line_po.po_line_number
LEFT JOIN supplier with (NOLOCK) ON supplier.supplier_id = po_hdr.supplier_id
ON oe_line_po.order_number = oe_hdr.order_no AND
oe_line_po.line_number = p21_view_oe_line.line_no 
AND
oe_line_po.delete_flag = 'N' AND
oe_line_po.cancel_flag = 'N' AND
oe_line_po.completed = 'N' AND
oe_line_po.connection_type = 'P'

WHERE 
( oe_hdr_salesrep.primary_salesrep = 'Y' ) 
AND ( p21_view_oe_line.delete_flag = 'N' ) 
AND ( p21_view_oe_line.cancel_flag = 'N' ) 
AND ( p21_view_oe_line.complete = 'N') AND (( oe_hdr.completed <> 'Y') OR ((oe_hdr.completed = 'Y') )) 
AND p21_view_oe_line.parent_oe_line_uid = '0'
AND (oe_hdr.rma_flag = 'N' ) 
AND ( oe_hdr.projected_order = 'N' )  
--and  (oe_hdr.order_type <> '1706')
--and (location_name = 'TriNova - Florida')
--AND prod_order_hdr.complete <> 'y'
-- and  datediff(day , oe_hdr.order_date, getdate())  >= 5
-- AND  ((oe_hdr.order_date + 5) <  GETDATE())
-- AND (p21_view_audit_trail_oe_hdr_1319.date_created < dateadd(dd,5,p21_view_audit_trail_oe_hdr_1319.date_created))
--AND p21_view_audit_trail_oe_hdr_1319.date_created > GETDATE()

Answer 1

The errors is because you have two columns with the same name :

oe_hdr.order_date,
po_hdr.order_date, 

You need to alias one of them

Same with

po_line.po_no,
oe_hdr.po_no,

Please ensure you format your code well so makes it easier to read

Easiest way to find them is to copy and paste your code into Notepad++ and double click on each column name.. notepad will highlight all instances of that word within the same document..