Skip to Content
-1

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

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 ":".

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • May 03 at 05:50 AM

    You enforce case sensitivity in HANA using double quotes to Column Names, such as in following example

    CREATE COLUMN TABLE "ABC" ("col1" INTEGER, "COL2" INTEGER)

    HERE you have two separate columns for Table ABC "col1' and "COL2", the case sensitivity is enforced by double quotes and yes you use ":" to assign variable value to column name.

    Add comment
    10|10000 characters needed characters exceeded

    • You have to use Declare statement while declaring variable for the sqlscript, HANA Supports SQL92 standard; post this change please print the error here what you are getting.

      DECLARE Period NVARCHAR(30);