Skip to Content
-1

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

May 03 at 02:16 AM

35

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

ANIRUDDHA SHINDE May 03 at 05:50 AM
0

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.

Show 2 Share
10 |10000 characters needed characters left characters exceeded

I get what you mean, what I try to understand is why my script doesn't work. BTW this is my script that I try to convert to hana

/* SELECT FROM OFPR T0 */
Period NVARCHAR(30)
/* WHERE */
Period := /* T0.Code */ '[%0]'
--SET Period := '2017-04'
Datex AS Date
Datex := (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" <= :Datex
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;

0

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);
0