Problema con parametros

[Former Member]

Hola que tal,

Tengo un query que me funciona correctamente mandando dos parametros de fecha, pero cuando quiero agregar un parametro llamado 'AlamcenN' SAP no me lo reconoce, incluso dejandolo como único parámetro quitando las fechas no quiere reconocerlo, ¿qué podría estar mal?

Select DISTINCT

          T0.AlamcenN As [PV],

          T0.Art As [Articulo],

          T0.[Nombre Art] As [Nombre],

          Sum(T0.Cantidad) As [Cantidad],

          Sum(T0.[Total]) As [Total],

          CASE WHEN(T0.Art IN (SELECT CODE FROM OITT)) THEN

          (Sum(T0.[Cantidad])*(SELECT SUM(ROUND(B.LastPurPrc*A.Quantity,2))

          FROM ITT1 A RIGHT JOIN OITM B ON A.Code = B.ItemCode

          WHERE A.Father = T0.Art)) ELSE

          (Sum(T0.[Cantidad])*T4.LastPurPrc) END AS [Tot Ult Precio Compra],

          (Sum(T0.[Total]) - CASE WHEN(T0.Art IN (SELECT CODE FROM OITT)) THEN

          (Sum(T0.[Cantidad])*(SELECT SUM(ROUND(B.LastPurPrc*A.Quantity,2))

          FROM ITT1 A RIGHT JOIN OITM B ON A.Code = B.ItemCode

          WHERE A.Father = T0.Art)) ELSE

          (Sum(T0.[Cantidad])*T4.LastPurPrc) END) [Ganancia],

          (((Sum(T0.[Total]) - CASE WHEN(T0.Art IN (SELECT CODE FROM OITT)) THEN

          (Sum(T0.[Cantidad])*(SELECT SUM(ROUND(B.LastPurPrc*A.Quantity,2))

          FROM ITT1 A RIGHT JOIN OITM B ON A.Code = B.ItemCode

          WHERE A.Father = T0.Art)) ELSE

          (Sum(T0.[Cantidad])*T4.LastPurPrc) END)/(Sum(T0.[Total])))*100) [Porcentaje]

From

          CXAlim T0

          Left Outer Join OINV T1 On T0.NumFact = T1.DocNum And T0.[Tipo Docto] = 'Factura'

          Left Outer Join ODLN T2 On T0.NumRem = T2.DocNum And T0.[Tipo Docto] = 'Remision'

          iNNER Join OITM T4 ON T0.Art = T4.ItemCode

Where

          (T1.DocDate >= [%0] And T1.DocDate <= [%1])

          Or

          (T2.DocDate >= [%0] And T2.DocDate <= [%1])

          AND

          (T0.AlamcenN = [%2])

Group By

          T0.AlamcenN,T0.Art, T0.[Nombre Art], T4.LastPurPrc

Saludos y muchas gracias de antemano!

[Former Member]

Los parametros tienen que ser sobre tablas de SAP Business One, te zaconsejo que crees una tabla de usuario con los datos de la tabla CXAlim, y sobre esta le des los parametros

[Former Member]

Lo que sucede es que es una vista y constantemente está cambiando, ¿no hay manera de utilizarlo como parámetro?

[Former Member]

Si entiendo bien deseas filtrar por la bodega.

¿Este codigo por el cual estas filtrando es el mismo que el de bodegas de SAP Business One?

Si es asi debieras cruzarla con la tabla owhs y filtrar por esta ultima

[Former Member]

Hice esto, y me trae almacenes incorrectos, ¿alguna idea del por qué?

Select DISTINCT

          T0.AlamcenN As [PV],

          T0.Art As [Articulo],

          T0.[Nombre Art] As [Nombre],

          Sum(T0.Cantidad) As [Cantidad],

          Sum(T0.[Total]) As [Total],

          CASE WHEN(T0.Art IN (SELECT CODE FROM OITT)) THEN

          (Sum(T0.[Cantidad])*(SELECT SUM(ROUND(B.LastPurPrc*A.Quantity,2))

          FROM ITT1 A RIGHT JOIN OITM B ON A.Code = B.ItemCode

          WHERE A.Father = T0.Art)) ELSE

          (Sum(T0.[Cantidad])*T4.LastPurPrc) END AS [Tot Ult Precio Compra],

          (Sum(T0.[Total]) - CASE WHEN(T0.Art IN (SELECT CODE FROM OITT)) THEN

          (Sum(T0.[Cantidad])*(SELECT SUM(ROUND(B.LastPurPrc*A.Quantity,2))

          FROM ITT1 A RIGHT JOIN OITM B ON A.Code = B.ItemCode

          WHERE A.Father = T0.Art)) ELSE

          (Sum(T0.[Cantidad])*T4.LastPurPrc) END) [Ganancia],

          (((Sum(T0.[Total]) - CASE WHEN(T0.Art IN (SELECT CODE FROM OITT)) THEN

          (Sum(T0.[Cantidad])*(SELECT SUM(ROUND(B.LastPurPrc*A.Quantity,2))

          FROM ITT1 A RIGHT JOIN OITM B ON A.Code = B.ItemCode

          WHERE A.Father = T0.Art)) ELSE

          (Sum(T0.[Cantidad])*T4.LastPurPrc) END)/(Sum(T0.[Total])))*100) [Porcentaje]

From

          CXAlim T0

          Left Outer Join OINV T1 On T0.NumFact = T1.DocNum And T0.[Tipo Docto] = 'Factura'

          Left Outer Join ODLN T2 On T0.NumRem = T2.DocNum And T0.[Tipo Docto] = 'Remision'

          iNNER Join OITM T4 ON T0.Art = T4.ItemCode

          inner JOIN OWHS T5 ON T0.AlmacenC = T5.WhsCode

Where

          (T1.DocDate >= [%0] And T1.DocDate <= [%1])

          Or

          (T2.DocDate >= [%0] And T2.DocDate <= [%1])

          AND

          (T5.WhsnAME= [%2])

Group By

          T0.AlamcenN,T0.Art, T0.[Nombre Art], T4.LastPurPrc

[Former Member]

Prueba lo siguiente:

declare

@bodega as nvarchar(100)

set @bodega =(select t10.whsname from owhs t10 where t10.WhsName=[%2])

Select DISTINCT

          T0.AlamcenN As [PV],

          T0.Art As [Articulo],

          T0.[Nombre Art] As [Nombre],

          Sum(T0.Cantidad) As [Cantidad],

          Sum(T0.[Total]) As [Total],

          CASE WHEN(T0.Art IN (SELECT CODE FROM OITT)) THEN

          (Sum(T0.[Cantidad])*(SELECT SUM(ROUND(B.LastPurPrc*A.Quantity,2))

          FROM ITT1 A RIGHT JOIN OITM B ON A.Code = B.ItemCode

          WHERE A.Father = T0.Art)) ELSE

          (Sum(T0.[Cantidad])*T4.LastPurPrc) END AS [Tot Ult Precio Compra],

          (Sum(T0.[Total]) - CASE WHEN(T0.Art IN (SELECT CODE FROM OITT)) THEN

          (Sum(T0.[Cantidad])*(SELECT SUM(ROUND(B.LastPurPrc*A.Quantity,2))

          FROM ITT1 A RIGHT JOIN OITM B ON A.Code = B.ItemCode

          WHERE A.Father = T0.Art)) ELSE

          (Sum(T0.[Cantidad])*T4.LastPurPrc) END) [Ganancia],

          (((Sum(T0.[Total]) - CASE WHEN(T0.Art IN (SELECT CODE FROM OITT)) THEN

          (Sum(T0.[Cantidad])*(SELECT SUM(ROUND(B.LastPurPrc*A.Quantity,2))

          FROM ITT1 A RIGHT JOIN OITM B ON A.Code = B.ItemCode

          WHERE A.Father = T0.Art)) ELSE

          (Sum(T0.[Cantidad])*T4.LastPurPrc) END)/(Sum(T0.[Total])))*100) [Porcentaje]

From

          CXAlim T0

          Left Outer Join OINV T1 On T0.NumFact = T1.DocNum And T0.[Tipo Docto] = 'Factura'

          Left Outer Join ODLN T2 On T0.NumRem = T2.DocNum And T0.[Tipo Docto] = 'Remision'

          iNNER Join OITM T4 ON T0.Art = T4.ItemCode

Where

          (T1.DocDate >= [%0] And T1.DocDate <= [%1])

          Or

          (T2.DocDate >= [%0] And T2.DocDate <= [%1])

          AND

          (T0.AlamcenN = @bodega)

Group By

          T0.AlamcenN,T0.Art, T0.[Nombre Art], T4.LastPurPrc

[Former Member]

Me sigue trayendo almacenes incorrectos, pero cuando lo hago separado como con el siguiente select si me los trae correctamente:

SELECT top 500 T0.AlamcenN, t1.WhsName

                      FROM CXALIM T0 INNER JOIN OWHS T1 ON T0.AlmacenC = T1.WhsCode

EDIT: Ya lo pude solucionar sólo tuve que acomodar los parentesis de esta forma:

Where

          ((T1.DocDate >= [%0] And T1.DocDate <= [%1])

          Or

          (T2.DocDate >= [%0] And T2.DocDate <= [%1]))

          AND

          T5.WhsName= '[%2]'

Gracias!