Multiple input parameters in HANA SQLScript calculation view

[Ibrahem]

Dear Experts,

I'm trying to select data within date interval but when I put more than one input parameter i get this error

Error: SAP DBTech JDBC: [2048]: column store error: search table error:  [34092] search on calculation model requires parameters;Required variable $$tdate$$ is not set.

My code:

/*********** Begin Procedure Script ************/ 
 BEGIN 
VAR_OUT = select RYEAR AS "FISCAL_YEAR",
  DOCNR AS "DOCUMENT_NUMBER",
  RBUKRS AS "COMPANY_CODE",
  RACCT AS "GL",
  "GLAccountName" AS "GL_NAME",
  "GLAccountDescription" AS "GL_DESC",
  F.PRCTR "PROFIT_CENTER_CODE",
  PC.KTEXT AS "PROFIT_CENTER_DESC",
  RCNTR AS "COST_CENTER_CODE",
  CC.KTEXT AS "COST_CENTER_DESC",
  TO_DATE(BUDAT,'YYYYMMDD') AS "POSTING_DATE",
  "IsBalanceSheetAccount" AS "ISBALANCESHEETACCOUNT",
  HSL AS LC_AMOUNT
  FROM "FAGLFLEXA" AS F inner join "CSKT" AS CC ON F.RCLNT = CC.MANDT AND F.RCNTR = CC.KOSTL
  INNER JOIN "CEPCT" AS PC ON F.RCLNT = PC.MANDT AND F.PRCTR = PC.PRCTR
  INNER JOIN "_SYS_BIC"."sap.hba.ecc/SKA1" AS GL ON F.RCLNT = GL."SAPClient" AND F.RACCT = GL."GLAccount"
  where F."BUDAT" >= TO_DATS(:FDATE)
  AND F."BUDAT" <=  TO_DATS(:TDATE)
  AND RCLNT = '900'
  ;




END 
/********* End Procedure Script ************/

Thank you in advance.

[former_member194892 · Accepted solution]

I had the same issue. Upgraded from 2.2.8 to 2.3.5 and now everything works as expected.

Hope this helps!

[Former Member]

Hello Ibrahem,

Try using BETWEEN operator in your script. It worked for me.

F.BUDAT BETWEEN FDATE AND TDATE;

[Ibrahem]

getting same error with my HANA Studio.

[Former Member]

what is the version of studio you are using? I am using 2.0.13

and called it in scripted view as below

[Ibrahem]

My HANA Studio version is 2.2.8, and I tried as you mentioned but it's didn't work, and like what Mr. Florian said generated SQL is considering only one parameter.

[pfefferf]

Hello Ibrahem,

how do you call and set the parameters of the scripted calculation view?

Regards,

Florian

[Ibrahem]

Input parameters are TDATE and FDATE are like this:

and using them after where clause

where F."BUDAT" >= TO_DATS(:FDATE)
  AND F."BUDAT" <=  TO_DATS(:TDATE)

and setting input parameter in

[pfefferf]

Seems like you found a bug. Get the same error with my HANA tools installation (v2.2.8).

Although both parameters are supplied, the generated SQL only considers only the second one.

Generated SQL:
SELECT TOP 200 min("DATE1") AS "DATE1", min("DATE2") AS "DATE2" FROM "_SYS_BIC"."test.misc.model/CVScripted_DateInputRange"('PLACEHOLDER' = ('$$IP_DATE2$$', '2016-04-30'))

I tested also the web-based tooling (HANA Web-based Development workbench). This works fine. Both entered parameters are considered and the view can be successfully executed.

Searched for an OSS note, but could not find anyone related to the topic. Maybe you should open a ticket if no one else can bring some light on this.

As workaround you can either use the web-based tooling or do the select with all parameters by yourself.

Independent of that maybe you should go with another approach because Scripted Calculation Views are not the future way to go. If you need scripted coding within a view, use a table function which can be consumed from a graphical calculation view.