Cuando intento configurar el conjunto de datos en SSRS IDE, me sale el error que se ve en la instantánea.

La consulta funciona totalmente bien en SQL Server Management Studio, me pregunto ¿de dónde me salen mal?!

La conexión a la DB está bien establecida.


OPCIONAL:

En caso de que desee echar un vistazo a mi consulta (demasiado largo), he comprobado que muy bien. Nada de malo en ello:

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
InformationsquelleAutor Beginner_Pal | 2010-10-12

2 Comentarios

  1. 39

    Después de formatear el script un poco, me di cuenta de que hay 2 columnas con el mismo nombre que usted está seleccionando. Asegúrese de cambiar el nombre final & de que cada columna cuando se ejecuta la instrucción en Management Studio tiene una único nombre.

    Que se dice, las dos columnas he dado cuenta de que han duplicado los nombres customerquoteproducts.istaxpaid y customershipping.istaxpaid

    Espero que ayude!

    • Veo, pero estos similares columnas residen en totalmente diferentes mesas! Menciono el prefijo, por lo que el sistema no debe ser confundido … Gracias por tu ayuda, voy a comprobarlo…
    • SOLUCIONADO ………….
    • sí, otra loca, ineficiente de error en Microsoft BI… yo también tenía el mismo problema y esto funcionó
    • Si usted envolver el exterior seleccione SMS le dará un duplicado de la columna nombre de advertencia y guardar algunos dolores de cabeza. SELECT * FROM ( <your select> ) results
  2. 0

    Me encontré con este post de hoy cuando he recibido el mismo problema.
    Mi problema, sin embargo, era un poco diferente.

    Tuve una tabla temporal y de una declaración de Si en mi SP.
    Así que mi SP parecía algo como esto:

    DECLARE @myCheck as int
    SET @myCheck = 0
    SELECT @myCheck = CASE WHEN [checkfield] = 'xxx' then 1 else 0 end
    FROM  checktable
    WHERE ...
    SELECT myfields.*
    into #temptable
    FROM mytable
    WHERE ...
    IF @myCheck = 1     --if ssrs does'nt want ot refresh your fields, remove hte if as it does not see past this...
    select *
    FROM #temptable
    LEFT JOIN tableA
    ON ...
    ELSE
    select *
    FROM #temptable
    LEFT JOIN tableB
    ON ...

    Entonces, para «actualizar» mis campos en SSRS, me comentó el Caso de declaración y conserva una de mis consultas con los nombres de campo.
    Sólo tenía que recordar todo de nuevo después…

    Utilizando Visual Studio 2012 /2012 de SQL

Dejar respuesta

Please enter your comment!
Please enter your name here