sent input parameters two times to the same procedure. Is it possible ?

[former_member507304]

Hi experts, I need your help

The fact is that a we need that when you put a year we have to store the previous 4 all of them as an input parameter. For example, to 2017 we need to get 2016, 2015, 2014 and 2013. In order to do this we have an input parameter that we put the 2017 and we call in the graphical view to a procedure that gets this value and returns its previous year to a new inputparameter. The problem that I have is that if I want to do the same now i can't send the 2016 year that i have in the new ip because it doesn't appear.

I'm thinking on 2 solutions but I need your experiencie and knowledge experts because I don't know if they are possible solutions or not.

One es is to go throguth calculation views and call it manually with the returned values (I don't know if this would work)

Second is to see if the procedure can return more than one value

Maybe a third would be to send this years to a table and get them from there.

What do you think ?

The script is the following:

CREATE PROCEDURE "_SYS_BIC".".HanaStudioSystemProject::ZGET_PREVIOUS_DATE" ( in ac_date varchar(4) , out prev_date varchar(4))
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
--DEFAULT SCHEMA <default_schema_name>
READS SQL DATA AS
BEGIN
/*****************************
Write your procedure logic
*****************************/
prev_date := to_char(to_int(ac_date)-1);
END;

Thanks for your attention.

[former_member507304]

The option of multiple outputs is not working cause I cant map de exit that I want.

I'm going to test the other option like a script view. I imagine that in a script view i will be able to create input parameters and call the functions all times i want Am I right ?

[kapil_tilwani · Accepted solution]

Hi Hector,

If the intention is to pass the value provided to the Input Parameter to be converted into 4 previous years, you may not really need a procedure for it. You could fire a select from M_TIME_DIMENSION_YEAR and fetch the last 3 values entered.

So, there shall be no procedures required but as a simple wrapper on the original view itself.

Use this tabular output as a JOIN instead of onward parameter passing to yield the desired results.

Regards,

Kapil

[pfefferf]

Ok, then I see one option - for the case that the number of years you go back are fix - to create an input parameter for each year with the option derived from the procedure. The procedure must have an additional argument to be able to define how many years should be subtracted from the original year entered. In the procedure parameter mapping you can pass the original entered year + a constant for the years you wanna subtract for the specific parameter.

Regards,
Florian

[pfefferf]

You are right, that you cannot map a table result value to an input parameter with the option "derived from procedure". But is it necessary that you have all the single years? Is it not enough to have the upper boundary (e.g. 2017) and the lower boundary (e.g. 2013) to be able to do your restrictions? In that case the "derived from procedure" option would be enough, because in your later selection you would restrict to the upper/lower boundary.

Regards,
Florian

[former_member507304]

Yes it is necesary because I call an underlevel view each time with a different year to get a lot of rates and data. If I do what you say I lose a lot of flexibility. But your answer is welcome maybe in another situation would be a good solution. Thanks