Input Variable from Procedure to Calc_View

[Former Member]

In code below, I get an error on the :v_period - ie the Procedure will not create. But if I hard code a period such as '2012012' then it works fine. I can't figure out how to get the input variable from the procedure to pass to the select statement. Any help would be appreciated. Thanks

---------------------

DROP procedure PR_UPDATE_COST;

CREATE PROCEDURE PR_UPDATE_COST( in v_period nvarchar(7))

LANGUAGE SQLSCRIPT AS BEGIN

DELETE  FROM "GROUPCOST";

INSERT INTO "GROUPCOST"(MM,PLANT,COST,CURR,PERIOD)

SELECT MATNR,WERKS,GROUP_COST,WAERS,PERIOD

from "_SYS_BIC"."pk-users.pk-pricing/CA_IJL_COST_BY_DATE"

('PLACEHOLDER'=('$$COST_DATE$$', :v_period));

insert INTO "LOGFILE" (Table,UserID,UpdateDay,UpdateTime)

SELECT 'GROUPCOST' as v1,CURRENT_USER,CURRENT_DATE,CURRENT_TIME FROM DUMMY;

END;

DELETE  FROM "GROUPCOST";

INSERT INTO "GROUPCOST"(MM,PLANT,COST,CURR,PERIOD)

SELECT MATNR,WERKS,GROUP_COST,WAERS,PERIOD

from "_SYS_BIC"."pk-users.pk-pricing/CA_IJL_COST_BY_DATE"

('PLACEHOLDER'=('$$COST_DATE$$', :v_period));

insert INTO "LOGFILE" (Table,UserID,UpdateDay,UpdateTime)

SELECT 'GROUPCOST' as v1,CURRENT_USER,CURRENT_DATE,CURRENT_TIME FROM DUMMY;

END;

[henrique_pinto · Accepted solution]

Unfortunately, you cannot stack input parameters in procedure calls.

Meaning, you cannot pass a variable (:<something>) in the PLACEHOLDER statement to another parameter. And apparently, that hasn't changed with SPS5...

Also, your parameter name in the PLACEHOLDER statement should be in lowercase (e.g. $$cost_date$$).

Check the SQLScript guide for more details.

http://help.sap.com/hana/hana_dev_sqlscript_en.pdf

(...) Please notice that no variable references are supported in the WITH PARAMETERS clause and that all constants are passed as string constants (i.e. enclosed in single quotes). Also, parameter names are lower case independent from the original capitalization in the procedure signature.

[Former Member · Accepted solution]

Thanks for your response Henrique. I was afraid I might be stuck here.

[Former Member · Accepted solution]

Since there's no details of the analytic view, it's hard to see what exactly the intent of the placeholders for "_SYS_BIC"."pk-users.pk-pricing/CA_IJL_COST_BY_DATE" are. But looks like filters... why not just include them in a WHERE clause?

Also, separate but related issue - input parameters from analytic views can be used in a Graphical CalcView. Scripted CalcView runs into the same problem as above though, no way to pass :var into the placeholder syntax of analytic view queries with placeholders. Another workaround to this though is hand-coding any calculated attributes/measures in the script with CE_CALC after querying Analytic View, but that's a bit complicated.

[Former Member · Accepted solution]

Jody, Using the WHERE clause is a good idea that I am able to do most of the time. In this particular case there is some filtering done way earlier in the calc view prior to the final output that kind of makes it impossible. But thanks for the suggestion.