cancel
Showing results for 
Search instead for 
Did you mean: 

How to pass a procedure (scalar procedure - returning a single value) to input parameter

Former Member
0 Kudos

Hi,

I have a simple procedure calculating Last Year Current Month(LYCM) - NVARCHAR(6) based on an input parameter(having Current Month This Year- an user selection).

How can I bind the value returned by this procedure to a second input parameter of NVARCHAR(6) derived from this procedure?

I am on HANA SP10

Thanks in Advance,

Prasad

Accepted Solutions (1)

Accepted Solutions (1)

pfefferf
Active Contributor
0 Kudos

What is your exact goal here? You wanna pass the value returned by the first procedure to a parameter of another procedure? In which area are you going to do this (SQLScript, XSJS, ...)? In all cases you have to use an intermediate variable, because a procedure cannot act directly as right hand side value for another procedure call.

Regards,

Florian

Former Member
0 Kudos

Thanks Florian for your quick response.

Yes, I want to pass the value returned by the procedure to a second input parameter. ( First input parameter is the one user enters a CALMONTH value)

The scenario is: I am creating a simple Calculation view in which one of the projection node need to be filtered based on the LYCM value returned from the Procedure;

pfefferf
Active Contributor
0 Kudos

The value returned from the procedure is automatically assigned to the parameter V_MONTH when the view is executed. Then of course the value is used for the filter you have defined using the V_MONTH parameter.

Former Member
0 Kudos

Its not working in that way in my case Florian. I am getting Empty Result upon execution. Even not getting any error and the mapping is also looks good; It seems am missing some thing silly.

For your reference Mapping:

Interestingly, the generated SQL (for data preview) has no mention to the V_MONTH input parameter, which is the one applied as filter to CALMONTH Column:

Generated SQL:

SELECT TOP 200 "DATE_SQL", "YEAR", "CALMONTH" FROM "_SYS_BIC"."avp/CA_DATES_LYCM"('PLACEHOLDER' = ('$$IN_CALMONTH$$', '201309'))

Prasad

Former Member
0 Kudos

Even i had faced similar kind of issue.

Try doing a DATA preview at the PROJECTION_1 node(lowest node where the filter has been applied). Are you able to view any data?

pfefferf
Active Contributor
0 Kudos

In the SQL the V_MONTH parameter is not mentioned because it is not of interest at that place. You are passing 201309 to IN_CALMONTH which is then passed to the procedure which determines the value for the V_MONTH parameter. That is done under the hood. Did you check what value the procedure returns? You can do this in a simple way, by e.g. adding a calculated column to the output which displays the value of the V_MONTH parameter.

Former Member
0 Kudos

Hi Florian, Thanks again;

The procedure is working;

A call to the procedure is returning results as expected:

Prasad

Former Member
0 Kudos

Hi Florian,

BTW, i have added a Calculated Column LYCM, but no luck with the outcome

Output:

Thanks

Prasad

Former Member
0 Kudos

Hi Deepak,

Thanks for your reply; But I am having issue with node level preview:

Prasad

pfefferf
Active Contributor
0 Kudos

The expression in your screenshot is not valid, it must be '$$V_MONTH$$'.

And of course you should remove the filter expression to see the value.

Silly question: The time dimension data is generated for the corresponding time frame you check?

Former Member
0 Kudos

Hi Florian,

The expression is OK. In the screen shot it got clipped while i rearrange to make the picture small.

I have relevant data in M_TIME_DIMENSION.

Prasad

Former Member
0 Kudos

Could you try creating the Scalar procedure under "_SYS_BIC" schema instead of "AVP" and try calling it in your view.

Former Member
0 Kudos

Hi Deepak,

Thanks for your tip. It has solved my Issue; I have created this procedure under _SYS_BIC and I am getting the output;

Output:

BTW, curios to know the reason; is that because of "avp" schema not having sufficient privileges?

Thank You once again,

Prasad

Answers (0)