sql - "An item with the same key has already been added"尝试设置数据集时 SSRS 出错

Question

当我尝试在 SSRS IDE 中设置数据集时，我收到您在快照中看到的错误。

查询在 SQL Server Management Studio 中工作得很好，我想知道我哪里出错了？!

与数据库的连接已建立良好。

<小时/>

可选:

如果您想查看我的查询(太长)，我检查得很好。没有什么问题:

SELECT Customer.customerID, Customer.companyName,  CustomerInvoice.dueDate, CustomerInvoice.cuInvoiceID, CustomerQuote.PONumber, Product.productName, CASE WHEN (SELECT     isTaxPaid
                                                     FROM         SupplierQuoteProducts
                                                     WHERE     productID = CustomerQuoteProducts.ProductID) = 1 THEN CustomerQuoteProducts.unitPrice * 1.15
                                                     WHEN (SELECT     isTaxPaid
                                                     FROM         SupplierQuoteProducts
                                                     WHERE     productID = CustomerQuoteProducts.ProductID) = 0 THEN CustomerQuoteProducts.unitPrice
                                                     ELSE CustomerQuoteProducts.unitPrice
                                                     END AS "unitPrice", 
                      CustomerQuoteProducts.qty, CustomerQuoteProducts.isTaxPaid, PaymentMethod.paymMethDesc, CustomerInvoice.customerQuoteID, CustomerInvDetail.paidDate, CustomerInvDetail.clearedDate,
                      CustomerInvDetail.notes, CustomerInvDetail.sentDate, PaymentExpected.payExpectedTitle, PaymentStatus.paymentStatusTitle, 
CASE WHEN
(SELECT     isTaxPaid
 FROM         SupplierQuoteProducts
 WHERE     productID = CustomerQuoteProducts.ProductID) = 1 AND CustomerQuoteProducts.qty > 0 AND 
CustomerQuoteProducts.isTaxPaid > 0 THEN (((CustomerQuoteProducts.unitPrice * 1.15) * 1.15) * CustomerQuoteProducts.qty) WHEN
(SELECT     isTaxPaid
 FROM         SupplierQuoteProducts
 WHERE     productID = CustomerQuoteProducts.ProductID) = 1 AND CustomerQuoteProducts.qty <= 0 AND 
CustomerQuoteProducts.isTaxPaid > 0 THEN ((CustomerQuoteProducts.unitPrice * 1.15) * 1.15) WHEN
(SELECT     isTaxPaid
 FROM         SupplierQuoteProducts
 WHERE     productID = CustomerQuoteProducts.ProductID) = 0 AND CustomerQuoteProducts.qty > 0 AND 
CustomerQuoteProducts.isTaxPaid > 0 THEN ((CustomerQuoteProducts.unitPrice * CustomerQuoteProducts.qty) * 1.15) WHEN
(SELECT     isTaxPaid
 FROM         SupplierQuoteProducts
 WHERE     productID = CustomerQuoteProducts.ProductID) = 0 AND CustomerQuoteProducts.qty <= 0 AND 
CustomerQuoteProducts.isTaxPaid > 0 THEN (CustomerQuoteProducts.unitPrice * 1.15) WHEN
(SELECT     Count(isTaxPaid)
 FROM         SupplierQuoteProducts
 WHERE     productID = CustomerQuoteProducts.ProductID) = 0 AND CustomerQuoteProducts.qty > 0 AND 
CustomerQuoteProducts.isTaxPaid > 0 THEN ((CustomerQuoteProducts.unitPrice * 1.15) * CustomerQuoteProducts.qty) WHEN
(SELECT     Count(isTaxPaid)
 FROM         SupplierQuoteProducts
 WHERE     productID = CustomerQuoteProducts.ProductID) = 0 AND CustomerQuoteProducts.qty <= 0 AND 
CustomerQuoteProducts.isTaxPaid > 0 THEN (CustomerQuoteProducts.unitPrice * 1.15) WHEN
(SELECT     isTaxPaid
 FROM         SupplierQuoteProducts
 WHERE     productID = CustomerQuoteProducts.ProductID) = 1 AND CustomerQuoteProducts.qty > 0 AND 
CustomerQuoteProducts.isTaxPaid <= 0 THEN (((CustomerQuoteProducts.unitPrice * 1.15)) * CustomerQuoteProducts.qty) WHEN
(SELECT     isTaxPaid
 FROM         SupplierQuoteProducts
 WHERE     productID = CustomerQuoteProducts.ProductID) = 1 AND CustomerQuoteProducts.qty <= 0 AND 
CustomerQuoteProducts.isTaxPaid <= 0 THEN ((CustomerQuoteProducts.unitPrice * 1.15)) WHEN
(SELECT     isTaxPaid
 FROM         SupplierQuoteProducts
 WHERE     productID = CustomerQuoteProducts.ProductID) = 0 AND CustomerQuoteProducts.qty > 0 AND 
CustomerQuoteProducts.isTaxPaid <= 0 THEN ((CustomerQuoteProducts.unitPrice * CustomerQuoteProducts.qty)) WHEN
(SELECT     isTaxPaid
 FROM         SupplierQuoteProducts
 WHERE     productID = CustomerQuoteProducts.ProductID) = 0 AND CustomerQuoteProducts.qty <= 0 AND 
CustomerQuoteProducts.isTaxPaid <= 0 THEN (CustomerQuoteProducts.unitPrice) WHEN
(SELECT     Count(isTaxPaid)
 FROM         SupplierQuoteProducts
 WHERE     productID = CustomerQuoteProducts.ProductID) = 0 AND CustomerQuoteProducts.qty > 0 AND 
CustomerQuoteProducts.isTaxPaid <= 0 THEN ((CustomerQuoteProducts.unitPrice * CustomerQuoteProducts.qty)) WHEN
(SELECT     Count(isTaxPaid)
 FROM         SupplierQuoteProducts
 WHERE     productID = CustomerQuoteProducts.ProductID) = 0 AND CustomerQuoteProducts.qty <= 0 AND 
CustomerQuoteProducts.isTaxPaid <= 0 THEN (CustomerQuoteProducts.unitPrice) END AS [TotalPrice], CASE WHEN row_number() OVER (partition BY 
CustomerInvoice.cuInvoiceId
ORDER BY newid()) = 1 THEN (CASE WHEN CustomerShipping.isTaxPaid > 0 THEN (CustomerShipping.shippingPrice * 1.15) 
WHEN CustomerShipping.isTaxPaid <= 0 THEN (CustomerShipping.shippingPrice) END) END AS [ShippingCost],
CASE WHEN row_number() OVER (partition BY 
CustomerInvoice.cuInvoiceId
ORDER BY newid()) = 1 THEN (CASE WHEN CustomerShipping.isTaxPaidForOrigPr > 0 THEN (CustomerShipping.origShipPrice * 1.15) 
WHEN CustomerShipping.isTaxPaidForOrigPr <= 0 THEN (CustomerShipping.origShipPrice) END) END AS [ShippingOrigCost],
CustomerShipping.isTaxPaid,
CustomerShipping.isTaxPaidForOrigPr,
CustomerShipping.shippingDate, CustomerShipping.trackingNumber, ShippingMethod.shippingVia, CustomerShipping.desAddress,
CustomerShipping.desCity, CustomerShipping.desPOBox, CustomerShipping.desPostalCode, CustomerShipping.desProvince, CustomerShipping.descName,
CustomerShipping.packageContent, CustomerShipping.cuShippingID, Country.countryName, CustomerShipping.packageDepth,
CustomerShipping.packageHeight, CustomerShipping.packageWeight, CustomerShipping.packageWidth, CustomerShipping.pickUpLocation
FROM         CustomerInvoice INNER JOIN
                      CustomerInvDetail ON CustomerInvoice.cuInvoiceID = CustomerInvDetail.cuInvoiceID INNER JOIN
                      CustomerQuote ON CustomerQuote.CustomerQuoteID = CustomerInvoice.CustomerQuoteID INNER JOIN
                      CustomerQuoteProducts ON CustomerQuoteProducts.CustomerQuoteID = CustomerQuote.CustomerQuoteID INNER JOIN
                      CustomerShipping ON CustomerShipping.CustomerQuoteID = CustomerInvoice.CustomerQuoteID INNER JOIN
                      PaymentStatus ON PaymentStatus.paymentStatusID = CustomerInvDetail.paymentStatusID INNER JOIN
                      Customer ON Customer.CustomerID = CustomerQuote.CustomerID INNER JOIN
                      Product ON CustomerQuoteProducts.productID = Product.productID INNER JOIN
                      Country ON Country.countryID = CustomerShipping.countryID INNER JOIN
                      ShippingMethod ON ShippingMethod.shippingMethodID = CustomerShipping.shippingMethodID INNER JOIN
                      PaymentExpected ON PaymentExpected.paymentExpectedID = CustomerInvDetail.paymentExpectedID INNER JOIN
                      PaymentMethod ON PaymentMethod.paymentMethodID = CustomerInvoice.paymentMethodID
WHERE  CustomerInvoice.cuInvoiceID = @cuInvID

Answer 1

对脚本进行一些格式化后，我注意到有 2 列与您选择的名称相同。确保更改最终名称，并且当您在 Management Studio 中运行语句时，每一列都有一个唯一名称。

话虽这么说，我注意到有重复名称的两列是 customerquoteproducts.istaxpaid 和 customershipping.istaxpaid

希望对您有所帮助!