Consulta Margenes

Buenas, tengo esta consulta:

En SAP hay un reporte llamado “Analisis de ventas por articulos”, este reporte trae (Numero de articulo, descripcion, cantidad, importe ventas, ganancia bruta, % ganancia bruta).

Lo que esta pidiendo el usuario es algo parecido, numero articulo, descripcion, cantidad, Precio, costo, Ganancia Bruta (Esta ganancia bruta tendria el mismo efecto que el margen de centro de beneficio, aqui seria la resta del precio de cada producto - el costo de los mismos), y el % de esa ganancia que iria con respecto al costo.

Aplico esta Query pero me genera el siguiente error:
1). [SAP AG][LIBODBCHDB32 DLL][HDBODBC32] Syntax error or access violation;257 sql syntax error: incorrect syntax near “Codigo”: line 1 col 18 ‘Valores definidos por usuario’ (CSHS) (at pos 18)

¿sabriais decirme cual es el error?

`SELECT A.ItemCode'Codigo', A.ItemName'Nombre Item', SUM(Cantidad)'Cantidad', SUM(Ventas)'Ventas', SUM(Costos)'Costos', SUM(Ganancia)'Ganancia Bruta', (SUM(A.Ganancia)/SUM(A.Ventas))*100 '%Ganancia'
FROM (
SELECT DISTINCT T0.ItemCode, T0.ItemName,
ISNULL((SELECT SUM(Y.Quantity) FROM OINV Z INNER JOIN INV1 Y ON Y.DocEntry=Z.DocEntry WHERE Z.DocDate BETWEEN '[%0]' AND '[%1]' AND Y.ItemCode=T0.ItemCode),0)'Cantidad',
ISNULL((SELECT SUM(Y.Quantity*Y.Price) FROM OINV Z INNER JOIN INV1 Y ON Y.DocEntry=Z.DocEntry WHERE Z.DocDate BETWEEN '[%0]' AND '[%1]' AND Y.ItemCode=T0.ItemCode),0)'Ventas',
ISNULL((SELECT SUM(Y.Quantity*Y.GrossBuyPr) FROM OINV Z INNER JOIN INV1 Y ON Y.DocEntry=Z.DocEntry WHERE Z.DocDate BETWEEN '[%0]' AND '[%1]' AND Y.ItemCode=T0.ItemCode),0)'Costos',
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 '[%0]' AND '[%1]' AND Y.ItemCode=T0.ItemCode),0)'Ganancia'
FROM OITM T0
UNION ALL 
SELECT DISTINCT T0.ItemCode, T0.ItemName,
ISNULL((SELECT -SUM(Y.Quantity) FROM ORIN Z INNER JOIN INV1 Y ON Y.DocEntry=Z.DocEntry WHERE Z.DocDate BETWEEN '[%0]' AND '[%1]' AND Y.ItemCode=T0.ItemCode),0)'Cantidad',
ISNULL((SELECT -SUM(Y.Quantity*Y.Price) FROM ORIN Z INNER JOIN INV1 Y ON Y.DocEntry=Z.DocEntry WHERE Z.DocDate BETWEEN '[%0]' AND '[%1]' AND Y.ItemCode=T0.ItemCode),0)'Ventas',
ISNULL((SELECT -SUM(Y.Quantity*Y.GrossBuyPr) FROM ORIN Z INNER JOIN INV1 Y ON Y.DocEntry=Z.DocEntry WHERE Z.DocDate BETWEEN '[%0]' AND '[%1]' AND Y.ItemCode=T0.ItemCode),0)'Costos',
ISNULL((SELECT -SUM(Y.Quantity*Y.Price)+SUM(Y.Quantity*Y.GrossBuyPr) FROM ORIN Z INNER JOIN INV1 Y ON Y.DocEntry=Z.DocEntry WHERE Z.DocDate BETWEEN '[%0]' AND '[%1]' AND Y.ItemCode=T0.ItemCode),0)'Ganancia'
FROM OITM T0
) AS A
WHERE A.Cantidad != 0 AND A.Ventas != 0 AND A.Costos != 0 AND A.Ganancia != 0
GROUP BY A.ItemCode, A.ItemName
ORDER BY 1`

@Olvir buenos días. Tu consulta debe ir en la sección de SAP Business One, esta sección es para consultas de ECC o S4. Por favor ayúdanos a mantener el orden en la comunidad.

Saludos.

Tienes razón, pido disculpas por el error,¿ podrías cerrarlo y lo abro de nuevo en el apartado SAP Business One?

Te lo cambio @Olvir a SAP BO

Hola,
Prueba a dejar un espacio entre el nombre del campo y el nombre que le quieras dar o bien poner un “AS” entre las dos cosas:
A.ItemCode ‘Codigo’
ó
A.ItemCode AS ‘Codigo’
Y eso en todos los campos a los que le asignes un nombre.
Saludos,

He realizado lo que indicas y me sigue apareciendo el siguiente error…

Syntax error or access violation;257 sql syntax error: incorrect syntax near “Codigo”: line 1 col 22 ‘Alertas recibidas’ (OAIB) (at pos 22)

SELECT A.ItemCode AS 'Codigo' , A.ItemName AS 'Nombre Item ', SUM(Cantidad) AS 'Cantidad' , SUM(Ventas) AS 'Ventas' , SUM(Costos) AS 'Costos' , SUM(Ganancia) AS 'Ganancia Bruta' , (SUM(A.Ganancia)/SUM(A.Ventas))*100 '%Ganancia'
FROM (
SELECT DISTINCT T0.ItemCode, T0.ItemName,
ISNULL ((SELECT SUM(Y.Quantity) FROM OINV Z INNER JOIN INV1 Y ON Y.DocEntry=Z.DocEntry WHERE Z.DocDate BETWEEN '[%0]' AND '[%1]' AND Y.ItemCode=T0.ItemCode),0) 'Cantidad',
ISNULL ((SELECT SUM(Y.Quantity*Y.Price) FROM OINV Z INNER JOIN INV1 Y ON Y.DocEntry=Z.DocEntry WHERE Z.DocDate BETWEEN '[%0]' AND '[%1]' AND Y.ItemCode=T0.ItemCode),0) 'Ventas',
ISNULL ((SELECT SUM(Y.Quantity*Y.GrossBuyPr) FROM OINV Z INNER JOIN INV1 Y ON Y.DocEntry=Z.DocEntry WHERE Z.DocDate BETWEEN '[%0]' AND '[%1]' AND Y.ItemCode=T0.ItemCode),0) 'Costos',
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 '[%0]' AND '[%1]' AND Y.ItemCode=T0.ItemCode),0) 'Ganancia'
FROM OITM T0
UNION ALL 
SELECT DISTINCT T0.ItemCode, T0.ItemName,
ISNULL ((SELECT -SUM(Y.Quantity) FROM ORIN Z INNER JOIN INV1 Y ON Y.DocEntry=Z.DocEntry WHERE Z.DocDate BETWEEN '[%0]' AND '[%1]' AND Y.ItemCode=T0.ItemCode),0) 'Cantidad',
ISNULL ((SELECT -SUM(Y.Quantity*Y.Price) FROM ORIN Z INNER JOIN INV1 Y ON Y.DocEntry=Z.DocEntry WHERE Z.DocDate BETWEEN '[%0]' AND '[%1]' AND Y.ItemCode=T0.ItemCode),0) 'Ventas',
ISNULL ((SELECT -SUM(Y.Quantity*Y.GrossBuyPr) FROM ORIN Z INNER JOIN INV1 Y ON Y.DocEntry=Z.DocEntry WHERE Z.DocDate BETWEEN '[%0]' AND '[%1]' AND Y.ItemCode=T0.ItemCode),0) 'Costos',
ISNULL ((SELECT -SUM(Y.Quantity*Y.Price)+SUM(Y.Quantity*Y.GrossBuyPr) FROM ORIN Z INNER JOIN INV1 Y ON Y.DocEntry=Z.DocEntry WHERE Z.DocDate BETWEEN '[%0]' AND '[%1]' AND Y.ItemCode=T0.ItemCode),0) 'Ganancia'
FROM OITM T0
) AS A
WHERE A.Cantidad != 0 AND A.Ventas != 0 AND A.Costos != 0 AND A.Ganancia != 0
GROUP BY A.ItemCode, A.ItemName
ORDER BY 1
1 me gusta

Hola, prueba con esta sentencia, básicamente he quitado las comillas de los parametros

SELECT A.ItemCode as 'Codigo', A.ItemName as 'Nombre Item', SUM(Cantidad) as 'Cantidad', SUM(Ventas) as 'Ventas', SUM(Costos) as 'Costos', SUM(Ganancia) as 'Ganancia Bruta', (SUM(A.Ganancia)/SUM(A.Ventas))*100 as '%Ganancia'
FROM (
SELECT DISTINCT T0.ItemCode, T0.ItemName,
ISNULL((SELECT SUM(Y.Quantity) FROM OINV Z INNER JOIN INV1 Y ON Y.DocEntry=Z.DocEntry WHERE Z.DocDate BETWEEN [%0] and [%1] AND Y.ItemCode=T0.ItemCode),0) as 'Cantidad',
ISNULL((SELECT SUM(Y.Quantity*Y.Price) FROM OINV Z INNER JOIN INV1 Y ON Y.DocEntry=Z.DocEntry WHERE Z.DocDate BETWEEN [%0] and [%1]  AND Y.ItemCode=T0.ItemCode),0) as 'Ventas',
ISNULL((SELECT SUM(Y.Quantity*Y.GrossBuyPr) FROM OINV Z INNER JOIN INV1 Y ON Y.DocEntry=Z.DocEntry WHERE Z.DocDate BETWEEN [%0] and [%1]  AND Y.ItemCode=T0.ItemCode),0) as 'Costos',
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 [%0] and [%1]  AND Y.ItemCode=T0.ItemCode),0) as 'Ganancia'
FROM OITM T0
UNION ALL 
SELECT DISTINCT T0.ItemCode, T0.ItemName,
ISNULL((SELECT -SUM(Y.Quantity) FROM ORIN Z INNER JOIN INV1 Y ON Y.DocEntry=Z.DocEntry WHERE Z.DocDate BETWEEN [%0] and [%1]  AND Y.ItemCode=T0.ItemCode),0) as 'Cantidad',
ISNULL((SELECT -SUM(Y.Quantity*Y.Price) FROM ORIN Z INNER JOIN INV1 Y ON Y.DocEntry=Z.DocEntry WHERE Z.DocDate BETWEEN [%0] and [%1]  AND Y.ItemCode=T0.ItemCode),0) as 'Ventas',
ISNULL((SELECT -SUM(Y.Quantity*Y.GrossBuyPr) FROM ORIN Z INNER JOIN INV1 Y ON Y.DocEntry=Z.DocEntry WHERE Z.DocDate BETWEEN [%0] and [%1]  AND Y.ItemCode=T0.ItemCode),0) as 'Costos',
ISNULL((SELECT -SUM(Y.Quantity*Y.Price)+SUM(Y.Quantity*Y.GrossBuyPr) FROM ORIN Z INNER JOIN INV1 Y ON Y.DocEntry=Z.DocEntry WHERE Z.DocDate BETWEEN [%0] and [%1]  AND Y.ItemCode=T0.ItemCode),0) as 'Ganancia'
FROM OITM T0
) AS A
WHERE A.Cantidad != 0 AND A.Ventas != 0 AND A.Costos != 0 AND A.Ganancia != 0
GROUP BY A.ItemCode, A.ItemName
ORDER BY 1

Sigue generando error… agradezco tu ayuda

Prueba asi

SELECT A."ItemCode" as "Codigo", A."ItemName" as "Nombre Item", SUM("Cantidad") as "Cantidad", SUM("Ventas") as "Ventas", SUM("Costos") as "Costos", SUM("Ganancia") as "Ganancia Bruta", (SUM(A."Ganancia")/SUM(A."Ventas"))*100 as "%Ganancia"
FROM (
SELECT DISTINCT T0."ItemCode", T0."ItemName",
ISNULL((SELECT SUM(Y."Quantity") FROM OINV Z INNER JOIN INV1 Y ON Y."DocEntry"=Z."DocEntry" WHERE Z."DocDate" BETWEEN [%0] and [%1] AND Y."ItemCode"=T0."ItemCode"),0) as "Cantidad",
ISNULL((SELECT SUM(Y."Quantity"*Y."Price") FROM OINV Z INNER JOIN INV1 Y ON Y."DocEntry"=Z."DocEntry" WHERE Z."DocDate" BETWEEN [%0] and [%1]  AND Y."ItemCode"=T0."ItemCode"),0) as "Ventas",
ISNULL((SELECT SUM(Y."Quantity"*Y."GrossBuyPr") FROM OINV Z INNER JOIN INV1 Y ON Y."DocEntry"=Z."DocEntry" WHERE Z."DocDate" BETWEEN [%0] and [%1]  AND Y."ItemCode"=T0."ItemCode"),0) as "Costos",
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 [%0] and [%1]  AND Y."ItemCode"=T0."ItemCode"),0) as "Ganancia"
FROM OITM T0
UNION ALL 
SELECT DISTINCT T0."ItemCode", T0."ItemName",
ISNULL((SELECT -SUM(Y."Quantity") FROM ORIN Z INNER JOIN INV1 Y ON Y."DocEntry"=Z."DocEntry" WHERE Z."DocDate" BETWEEN [%0] and [%1]  AND Y."ItemCode"=T0."ItemCode"),0) as "Cantidad",
ISNULL((SELECT -SUM(Y."Quantity"*Y."Price") FROM ORIN Z INNER JOIN INV1 Y ON Y."DocEntry"=Z."DocEntry" WHERE Z."DocDate" BETWEEN [%0] and [%1]  AND Y."ItemCode"=T0."ItemCode"),0) as "Ventas",
ISNULL((SELECT -SUM(Y."Quantity"*Y."GrossBuyPr") FROM ORIN Z INNER JOIN INV1 Y ON Y."DocEntry"=Z."DocEntry" WHERE Z."DocDate" BETWEEN [%0] and [%1]  AND Y."ItemCode"=T0."ItemCode"),0) as "Costos",
ISNULL((SELECT -SUM(Y."Quantity"*Y."Price")+SUM(Y."Quantity"*Y."GrossBuyPr") FROM ORIN Z INNER JOIN INV1 Y ON Y."DocEntry"=Z."DocEntry" WHERE Z."DocDate" BETWEEN [%0] and [%1]  AND Y."ItemCode"=T0."ItemCode"),0) as "Ganancia"
FROM OITM T0
) AS A
WHERE A."Cantidad" != 0 AND A."Ventas" != 0 AND A."Costos" != 0 AND A."Ganancia" != 0
GROUP BY A."ItemCode", A."ItemName"
ORDER BY 1

Si la consulta es en Hana tienes que poner doble comilla tanto en los campos como en el nombre que le asignas al campo.

Yo la he provado en un SAP SQL y tanto en SQL Server como en la generación de consultas de SAP me ha funcionado bien.

Donde la estás ejecutando?

Lo estoy ejecutando a través de SAP, Query Manager, ya que no tengo tantos conocimientos para ejecutar desde otro lado (aunque si acceso si supiese como)

Aunque ahora me da el error

General error;266 inconsistent datatype: DECIMAL or INT are invalid function argument types: line 4 col 1 ‘Acuerdo global’ (OOAT) (at pos 294)

Supongo que el error debe venir de que tienes algun registro con valor nulo. Prueba a poner la función ISNULL en los campos de valores en vez de en toda la sentencia.
Por ejemplo
SUM(ISNULL(Y.Quantity,0) * ISNULL(Y.Price,0))

Sigue dando error, algo no realizo bien

SELECT A."ItemCode" as "Codigo", A."ItemName" as "Nombre Item", SUM("Cantidad") as "Cantidad", SUM("Ventas") as "Ventas", SUM("Costos") as "Costos", SUM("Ganancia") as "Ganancia Bruta", (SUM(A."Ganancia")/SUM(A."Ventas"))*100 as "%Ganancia"
FROM (
SELECT DISTINCT T0."ItemCode", T0."ItemName",
((SELECT SUM(ISNULL(Y."Quantity")) FROM OINV Z INNER JOIN INV1 Y ON Y."DocEntry"=Z."DocEntry" WHERE Z."DocDate" BETWEEN [%0] and [%1] AND Y."ItemCode"=T0."ItemCode"),0) as "Cantidad",
((SELECT SUM(ISNULL(Y."Quantity"))*(ISNULL(Y."Price")) FROM OINV Z INNER JOIN INV1 Y ON Y."DocEntry"=Z."DocEntry" WHERE Z."DocDate" BETWEEN [%0] and [%1]  AND Y."ItemCode"=T0."ItemCode"),0) as "Ventas",
((SELECT SUM(ISNULL(Y."Quantity"))*(ISNULL(Y."GrossBuyPr")) FROM OINV Z INNER JOIN INV1 Y ON Y."DocEntry"=Z."DocEntry" WHERE Z."DocDate" BETWEEN [%0] and [%1]  AND Y."ItemCode"=T0."ItemCode"),0) as "Costos",
((SELECT SUM(ISNULL(Y."Quantity"))*(ISNULL(Y."Price"))-SUM(ISNULL(Y."Quantity"))*(ISNULL(Y."GrossBuyPr")) FROM OINV Z INNER JOIN INV1 Y ON Y."DocEntry"=Z."DocEntry" WHERE Z."DocDate" BETWEEN [%0] and [%1]  AND Y."ItemCode"=T0."ItemCode"),0) as "Ganancia"
FROM OITM T0
UNION ALL 
SELECT DISTINCT T0."ItemCode", T0."ItemName",
((SELECT -SUM(ISNULL(Y."Quantity")) FROM ORIN Z INNER JOIN INV1 Y ON Y."DocEntry"=Z."DocEntry" WHERE Z."DocDate" BETWEEN [%0] and [%1]  AND Y."ItemCode"=T0."ItemCode"),0) as "Cantidad",
((SELECT -SUM(ISNULL(Y."Quantity"))*Y."Price")) FROM ORIN Z INNER JOIN INV1 Y ON Y."DocEntry"=Z."DocEntry" WHERE Z."DocDate" BETWEEN [%0] and [%1]  AND Y."ItemCode"=T0."ItemCode"),0) as "Ventas",
((SELECT -SUM(ISNULL(Y."Quantity"))*Y."GrossBuyPr")) FROM ORIN Z INNER JOIN INV1 Y ON Y."DocEntry"=Z."DocEntry" WHERE Z."DocDate" BETWEEN [%0] and [%1]  AND Y."ItemCode"=T0."ItemCode"),0) as "Costos",
((SELECT -SUM(ISNULL(Y."Quantity"))*Y."Price"))+SUM((ISNULL(Y."Quantity")*(ISNULL(Y."GrossBuyPr")) FROM ORIN Z INNER JOIN INV1 Y ON Y."DocEntry"=Z."DocEntry" WHERE Z."DocDate" BETWEEN [%0] and [%1]  AND Y."ItemCode"=T0."ItemCode"),0) as "Ganancia"
FROM OITM T0
) AS A
WHERE A."Cantidad" != 0 AND A."Ventas" != 0 AND A."Costos" != 0 AND A."Ganancia" != 0
GROUP BY A."ItemCode", A."ItemName"
ORDER BY 1

El acuerdo global no aparece en tu query, como es que te marca ese error?

Te falta la coma y el 0 del ISNULL
Tiene que ser así:
ISNULL(Y.“Quantity”,0)

Esta función lo que hace es que si el campo Quantity viene sin valor, le asigne un 0

Es un error “Global”. Este error normalmente he visto que sale cuando hay un error de sintaxis en la consulta.

Aca esta mencionando otra cosa

Buenas, tras probar sigue generandome error, no le veo solucion…

SELECT A.“ItemCode” as “Codigo”, A.“ItemName” as “Nombre Item”, SUM(“Cantidad”) as “Cantidad”, SUM(“Ventas”) as “Ventas”, SUM(“Costos”) as “Costos”, SUM(“Ganancia”) as “Ganancia Bruta”, (SUM(A.“Ganancia”)/SUM(A.“Ventas”))100 as “%Ganancia”
FROM (
SELECT DISTINCT T0.“ItemCode”, T0.“ItemName”,
((SELECT SUM(ISNULL(Y.“Quantity”,0)) FROM OINV Z INNER JOIN INV1 Y ON Y.“DocEntry”=Z.“DocEntry” WHERE Z.“DocDate” BETWEEN [%0] and [%1] AND Y.“ItemCode”=T0.“ItemCode”),0) as “Cantidad”,
((SELECT SUM(ISNULL(Y.“Quantity”,0))(ISNULL(Y.“Price”,0)) FROM OINV Z INNER JOIN INV1 Y ON Y.“DocEntry”=Z.“DocEntry” WHERE Z.“DocDate” BETWEEN [%0] and [%1] AND Y.“ItemCode”=T0.“ItemCode”),0) as “Ventas”,
((SELECT SUM(ISNULL(Y.“Quantity”,0))(ISNULL(Y.“GrossBuyPr”,0)) FROM OINV Z INNER JOIN INV1 Y ON Y.“DocEntry”=Z.“DocEntry” WHERE Z.“DocDate” BETWEEN [%0] and [%1] AND Y.“ItemCode”=T0.“ItemCode”),0) as “Costos”,
((SELECT SUM(ISNULL(Y.“Quantity”,0))(ISNULL(Y.“Price”,0))-SUM(ISNULL(Y.“Quantity”))*(ISNULL(Y.“GrossBuyPr”)) FROM OINV Z INNER JOIN INV1 Y ON Y.“DocEntry”=Z.“DocEntry” WHERE Z.“DocDate” BETWEEN [%0] and [%1] AND Y.“ItemCode”=T0.“ItemCode”),0) as “Ganancia”
FROM OITM T0
UNION ALL
SELECT DISTINCT T0.“ItemCode”, T0.“ItemName”,
((SELECT -SUM(ISNULL(Y.“Quantity”,0)) FROM ORIN Z INNER JOIN INV1 Y ON Y.“DocEntry”=Z.“DocEntry” WHERE Z.“DocDate” BETWEEN [%0] and [%1] AND Y.“ItemCode”=T0.“ItemCode”),0) as “Cantidad”,
((SELECT -SUM(ISNULL(Y.“Quantity”,0))*Y.“Price”,0)) FROM ORIN Z INNER JOIN INV1 Y ON Y.“DocEntry”=Z.“DocEntry” WHERE Z.“DocDate” BETWEEN [%0] and [%1] AND Y.“ItemCode”=T0.“ItemCode”),0) as “Ventas”,
((SELECT -SUM(ISNULL(Y.“Quantity”,0))*Y.“GrossBuyPr”,0)) FROM ORIN Z INNER JOIN INV1 Y ON Y.“DocEntry”=Z.“DocEntry” WHERE Z.“DocDate” BETWEEN [%0] and [%1] AND Y.“ItemCode”=T0.“ItemCode”),0) as “Costos”,
((SELECT -SUM(ISNULL(Y.“Quantity”,0))Y.“Price”,0))+SUM((ISNULL(Y.“Quantity”)(ISNULL(Y.“GrossBuyPr”)) FROM ORIN Z INNER JOIN INV1 Y ON Y.“DocEntry”=Z.“DocEntry” WHERE Z.“DocDate” BETWEEN [%0] and [%1] AND Y.“ItemCode”=T0.“ItemCode”),0) as “Ganancia”
FROM OITM T0
) AS A
WHERE A.“Cantidad” != 0 AND A.“Ventas” != 0 AND A.“Costos” != 0 AND A.“Ganancia” != 0
GROUP BY A.“ItemCode”, A.“ItemName”
ORDER BY 1

Podrías pasar la captura de la forma en que estas ejecutando y cual es el mensaje del error?

Captura-de-pantalla-2021-05-28-141353