Question Re: Multiple input parameters in HANA SQLScript calculation view in Technology Q&A

Multiple input parameters in HANA SQLScript calculation view

Ibrahem — Sat, 02 Apr 2016 08:38:01 GMT

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.

Re: Multiple input parameters in HANA SQLScript calculation view

pfefferf — Sat, 02 Apr 2016 08:53:22 GMT

Hello Ibrahem,

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

Regards,

Florian

Re: Multiple input parameters in HANA SQLScript calculation view

Ibrahem — Sat, 02 Apr 2016 09:02:08 GMT

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

Re: Multiple input parameters in HANA SQLScript calculation view

pfefferf — Sat, 02 Apr 2016 09:55:30 GMT

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.

Re: Multiple input parameters in HANA SQLScript calculation view

Former Member — Sat, 02 Apr 2016 16:38:52 GMT

Hello Ibrahem,

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

F.BUDAT BETWEEN FDATE AND TDATE;

Re: Multiple input parameters in HANA SQLScript calculation view

Ibrahem — Sun, 03 Apr 2016 05:08:22 GMT

getting same error with my HANA Studio.

Re: Multiple input parameters in HANA SQLScript calculation view

Former Member — Sun, 03 Apr 2016 07:15:20 GMT

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

and called it in scripted view as below

Re: Multiple input parameters in HANA SQLScript calculation view

Ibrahem — Tue, 05 Apr 2016 08:43:54 GMT

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.

Re: Multiple input parameters in HANA SQLScript calculation view

former_member194892 — Tue, 05 Jul 2016 17:45:37 GMT

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

Hope this helps!