Skip to Content
-1
May 03, 2018 at 02:16 AM

SAP Business One HANA Query select by period (YYYY-MM)

1047 Views

I have this script

/* SELECT FROM [dbo].[OFPR] T0 */
DECLARE @Period AS NVARCHAR(30)
/* WHERE */
SET @Period = /* T0.Code */ '[%0]'
--SET @Period = '2017-04'
DECLARE @Date AS Date
SET @Date = (SELECT T_RefDate FROM OFPR WHERE Code = @Period);

SELECT 
    A.ItemCode, A.DistNumber,
    SUM(CASE A.ActionType WHEN 1 THEN A.Quantity WHEN 2 THEN  -1 * A.Quantity ELSE 0 END) as Quantity,
    SUM(A.InvValue) as Value
FROM OBVL A
    JOIN OIVL B ON A.ILMEntry = B.MessageID
    JOIN OITM C ON A.ItemCode = C.ItemCode
WHERE C.ItmsGrpCod = '103' AND B.DocDate <= @Date
GROUP BY A.ItemCode, A.DistNumber
HAVING SUM(CASE A.ActionType WHEN 1 THEN A.Quantity WHEN 2 THEN  -1 * A.Quantity ELSE 0 END) <> 0;

In SAP B1 that use SQL server it's works fine but now I need to convert it into HANA DB. I need to display the data based on period. So the user only need to input year and month (YYYY-MM). I have add all double quotes to every column because as we know HANA is case sensitive. and change the variable format from "@" to ":".