Estimados:
Tengo una query o consulta que al ejecutarla me envia el siguiente error:
1). [Microsoft][SQL Server Native Client 10.0][SQL Server]Error converting data type nvarchar to numeric. 'Valores definidos por usuario' (CSHS)
El problema se presenta con un campo definido por el usuario que es alfanumerico de largo 50.
Entiendo que debo convertirlo a un valor numerico, pero no he logrado poder hacerlo. Uds. me podrian ayudar a solucionar mi problema?
Adjunto la consulta y destaque con letra roja el campo que me presenta el problema y que necesito convertir a número.
DECLARE @VAR INT, @INI DATETIME, @FIN DATETIME
SET @VAR=(SELECT TOP 1 A.TransId FROM JDT1 A WHERE A.RefDate BETWEEN '[%0]' AND '[%1]')
SET @INI='[%0]'
SET @FIN='[%1]'
SELECT T0.[ItemCode], T0.[ItemName],
ISNULL((SELECT (SUM(Y.InQty)-SUM(Y.OutQty)) FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND Y.DocDate<@INI),0) 'Stock a la fecha inicio',
ISNULL((SELECT (SUM(Y.InQty)-SUM(Y.OutQty)) FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND Y.DocDate <= @FIN),0) 'Stock a la fecha termino',
ISNULL((SELECT SUM(TransValue) FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND Y.DocDate <= @FIN),0)'Stock Valorizado',
ISNULL((SELECT SUM(Y.Quantity) FROM OINV X INNER JOIN INV1 Y ON Y.DocEntry=X.DocEntry WHERE Y.ItemCode=T0.ItemCode AND X.DocDate >=@INI AND X.DocDate<=@FIN),0)-ISNULL((SELECT SUM(Y.Quantity) FROM ORIN X INNER JOIN RIN1 Y ON Y.DocEntry=X.DocEntry WHERE Y.ItemCode=T0.ItemCode AND X.DocDate >=@INI AND X.DocDate<=@FIN),0)'Cantidad Vendida',
DATEDIFF (dd, @INI, @FIN)'Nº de dias segun fechas',
CASE
WHEN ISNULL((SELECT SUM(Y.Quantity) FROM OINV X INNER JOIN INV1 Y ON Y.DocEntry=X.DocEntry WHERE Y.ItemCode=T0.ItemCode AND X.DocDate >=@INI AND X.DocDate<=@FIN),0)-ISNULL((SELECT SUM(Y.Quantity) FROM ORIN X INNER JOIN RIN1 Y ON Y.DocEntry=X.DocEntry WHERE Y.ItemCode=T0.ItemCode AND X.DocDate >=@INI AND X.DocDate<=@FIN),0) <= 0 THEN 0
ELSE ((ISNULL((SELECT SUM(Y.Quantity) FROM OINV X INNER JOIN INV1 Y ON Y.DocEntry=X.DocEntry WHERE Y.ItemCode=T0.ItemCode AND X.DocDate >=@INI AND X.DocDate<=@FIN),0)-ISNULL((SELECT SUM(Y.Quantity) FROM ORIN X INNER JOIN RIN1 Y ON Y.DocEntry=X.DocEntry WHERE Y.ItemCode=T0.ItemCode AND X.DocDate >=@INI AND X.DocDate<=@FIN),0))/(DATEDIFF (dd, @INI, @FIN)))
END 'Venta diaria',
CASE
WHEN ISNULL((SELECT (SUM(Y.InQty)-SUM(Y.OutQty)) FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND Y.DocDate <= @FIN),0) <= 0 THEN 0
ELSE ((ISNULL((SELECT (SUM(Y.InQty)-SUM(Y.OutQty)) FROM OINM Y WHERE Y.ItemCode = T0.ItemCode AND Y.DocDate <= @FIN),0)/(((ISNULL((SELECT SUM(Y.Quantity) FROM OINV X INNER JOIN INV1 Y ON Y.DocEntry=X.DocEntry WHERE Y.ItemCode=T0.ItemCode AND X.DocDate >=@INI AND X.DocDate<=@FIN),0)-ISNULL((SELECT SUM(Y.Quantity) FROM ORIN X INNER JOIN RIN1 Y ON Y.DocEntry=X.DocEntry WHERE Y.ItemCode=T0.ItemCode AND X.DocDate >=@INI AND X.DocDate<=@FIN),0))/(DATEDIFF (dd, @INI, @FIN))))))
END 'Inventario en dias',
ISNULL((SELECT SUM(Y.Quantity*Y.U_Precio_FE) FROM OINV X INNER JOIN INV1 Y ON Y.DocEntry=X.DocEntry WHERE Y.ItemCode=T0.ItemCode AND X.DocDate >=@INI AND X.DocDate<=@FIN),0)-ISNULL((SELECT SUM(Y.Quantity*Y.U_Precio_FE) FROM ORIN X INNER JOIN RIN1 Y ON Y.DocEntry=X.DocEntry WHERE Y.ItemCode=T0.ItemCode AND X.DocDate >=@INI AND X.DocDate<=@FIN),0)'Ventas',
ISNULL((SELECT SUM(Y.Quantity*Y.U_Precio_FE)-SUM(Y.Quantity*Y.GrossBuyPr) FROM OINV Z INNER JOIN INV1 Y ON Y.DocEntry=Z.DocEntry WHERE Z.DocDate BETWEEN @INI AND @FIN AND Y.ItemCode=T0.ItemCode),0)-ISNULL((SELECT SUM(Y.Quantity*Y.U_Precio_FE)-SUM(Y.Quantity*Y.GrossBuyPr) FROM ORIN Z INNER JOIN RIN1 Y ON Y.DocEntry=Z.DocEntry WHERE Z.DocDate BETWEEN @INI AND @FIN AND Y.ItemCode=T0.ItemCode),0)'Ganancia bruta',
((ISNULL((SELECT SUM(Y.Quantity*Y.Price)-SUM(Y.Quantity*Y.GrossBuyPr) FROM OINV Z INNER JOIN INV1 Y ON Y.DocEntry=Z.DocEntry WHERE Z.DocDate BETWEEN @INI AND @FIN AND Y.ItemCode=T0.ItemCode),0)-ISNULL((SELECT SUM(Y.Quantity*Y.Price)-SUM(Y.Quantity*Y.GrossBuyPr) FROM ORIN Z INNER JOIN RIN1 Y ON Y.DocEntry=Z.DocEntry WHERE Z.DocDate BETWEEN @INI AND @FIN AND Y.ItemCode=T0.ItemCode),0))/(ISNULL((SELECT SUM(Y.Quantity*Y.Price) FROM OINV X INNER JOIN INV1 Y ON Y.DocEntry=X.DocEntry WHERE Y.ItemCode=T0.ItemCode AND X.DocDate >=@INI AND X.DocDate<=@FIN),0)-ISNULL((SELECT SUM(Y.Quantity*Y.Price) FROM ORIN X INNER JOIN RIN1 Y ON Y.DocEntry=X.DocEntry WHERE Y.ItemCode=T0.ItemCode AND X.DocDate >=@INI AND X.DocDate<=@FIN),0)))*100 '% Ganancia'
FROM OITM T0
WHERE /*ISNULL((SELECT (SUM(Y.InQty)-SUM(Y.OutQty)) FROM OINM Y WHERE Y.ItemCode = T2.ItemCode AND Y.DocDate <= '[%1]'),0) != 0 AND*/
ISNULL((SELECT SUM(Y.Quantity*Y.Price) FROM OINV X INNER JOIN INV1 Y ON Y.DocEntry=X.DocEntry WHERE Y.ItemCode=T0.ItemCode AND X.DocDate >='[%0]' AND X.DocDate<='[%1]'),0)-ISNULL((SELECT SUM(Y.Quantity*Y.Price) FROM ORIN X INNER JOIN RIN1 Y ON Y.DocEntry=X.DocEntry WHERE Y.ItemCode=T0.ItemCode AND X.DocDate >='[%0]' AND X.DocDate<='[%1]'),0) != 0 AND T0.[ItmsGrpCod]=105
ORDER BY T0.[ItemCode]
Muchas Gracias.
Viviana Medina.
Comments