Incorrect syntax near 'union'

[former_member807378]

Tengo una consulta que desde SQL SERVER ejecuta correctamente

pero al momento de ejecutarla por el SERVICE LAYER

https://192.XXX.1.X:50000/b1s/v1/SQLQueries

{

"SqlCode": "SQLLotesPrueba",

"SqlName": "PruebaLote_2",

"SqlText": "SELECT BatchNum,Indate,Quantity FROM OIBT WHERE Quantity <> 0 and WhsCode = 'PV-ENV' and ItemCode = 'PT-1485' group by SysNumber,WhsCode,BatchNum,ItemCode,Indate,Quantity UNION ALL select '' as batchnum,'2022-07-05' as InDate,SUM(InQty)-SUM(OutQty) as Quantity FROM OIVL WHERE LocCode = 'PV-ENV' AND ItemCode = 'PT-1485' AND NOT EXISTS (SELECT itemcode FROM OIBT) GROUP BY LocCode,itemcode"

}

Esto es lo que me devuelve , ¿Service layer no soporta el ALL UNION o por que pasa esto ?

{

"error": {

"code": 701,

"message": {

"lang": "en-us",

"value": "Invalid SQL syntax:SELECT BatchNum,Indate,Quantity FROM OIBT WHERE Quantity <> 0 and WhsCode = 'PV-ENV' and ItemCode = 'PT-1485' GROUP BY SysNumber,WhsCode,BatchNum,ItemCode,Indate,Quantity union all SELECT '' as BatchNum,'2022-07-05' as InDate,SUM(InQty)-SUM(OutQty) as Quantity FROM OIVL WHERE LocCode = 'PV-ENV' AND ItemCode = 'PT-1485' AND NOT EXISTS (SELECT itemcode FROM OIBT) GROUP BY LocCode,itemcode, line 1, character position 171, Incorrect syntax near 'union'."

}

}

}

[ANKIT_CHAUHAN]

Hi jegiraldo60,

The mentioned query represents an arithmetic operation which is not supported in SQLQueries.

SAP Business One uses the Customer Influence site https://influence.sap.com/go/SAPBusinessOne for collecting, tracking, and prioritizing of requirements.

Please see and follow the recommendations in SAP Note 1028874, section B to post your requirement in SAP Business One Customer Influence site.

Hope it helps!

Kind regards,

ANKIT CHAUHAN

SAP Business One Support

[former_member807378]

Oh thank you