Passing a Input Parameter to a Calculation View

[Former Member]

We have a SQL calculation view, "ahcms.md/EMPLOYEE_T", at requires the input parameter KEYDATE ;

	PA0001.begda <= :KEYDATE and PA0001.endda >= KEYDATE and
	PA0002.begda <= :KEYDATE and PA0002.endda >= KEYDATE

We are calling this calculation view from another calculation view;

	from
		"_SYS_BIC"."ahcms.md/EMPLOYEE_T"

However we are not able to pass the parameter required.

Anyone know how to do this?

[Former Member]

Thanks for the input.

We were able to create a Procedure that selects data from the tables based on an input parameter. See code of the Procedure below;

BEGIN

            employee =

select

          PA0001.PERNR as PERNR,

          PA0002.GESCH as GESCH

from

          "_SYS_BIC"."ahcms.md/PA0001" as PA0001,

          "_SYS_BIC"."ahcms.md/PA0002" as PA0002

where

          PA0001.begda <= :KEYDATE2 and PA0001.endda >= :KEYDATE2 and

          PA0002.begda <= :KEYDATE2 and PA0002.endda >= :KEYDATE2 and PA0001.pernr = PA0002.pernr

          ;

END;

KEYDATE2 is identified as an Input Parameter in the Procedure Input Pane.

We are able to call this procedure from a Calculation View and pass a value to KEYDATE2. Code from CV;

call "_SYS_BIC"."ahcms.pa/EMPLOYEE_PRO" ( KEYDATE ,var_out);

KEYDATE is defined as as an Input Parameter in the Calculation View.

When we run the CV it asks for KEYDATE and then runs the Procedure with that KEYDATE.

Great!

Now we would like to combine the output of the Procedure with other data we have selected in the CV.

Any ideas?

[former_member184768]

Hi Gerhard,

One question, why not write the entire logic in one SQL procedure and use it in a calc view. Since your base calc view is also SQL script based, it would be easier to write the entire logic in a single procedure and use it.

Secondly you are combining Information models (Analytic Views  / Calc view like "_SYS_BIC"."ahcms.md/PA0001") with SQL script. From performance perspective then may not be recommended. It is suggested to avoid such combinations and hence it would be better if you write your entire logic in a single procedure using the base tables and use it in a single Information model (Calc View).

Regards,

Ravi

[Former Member]

Ravi

You're absolutely right.

We ditched the idea to use a procedure and combined everything in a single SQL calculation view script.

Thanks!

Gerhard

[former_member184768]

Hi Gerhard,

If you are trying to use the existing Calc view in another SQL based calc view, then as you mentioned, it will have to be used like "_SYS_BIC"."ahcms.md/EMPLOYEE_T"  and the PARAMETERS will have to be passed with WITH PARAMETERS clause. This clause takes the values enclosed in single quotes and not variables.

But as an alternative, can you please try using the PROCEDURE created by the SQL based Calc view. Please check if you can locate the Procedure under _SYS_BIC with "ahcms.md/EMPLOYEE_T/proc" name. Check if you can call this procedure with the input and output (var_out) and can use this option in the second calc view SQL.

I haven't tried it myself, but it is worth the try.

Regards,

Ravi

[Former Member]

Hi Gerard,

You can create a SQL based Calc View and that will simplify your scenario to a great extent. You can easily pass/store and use the variable.

/bikas