cancel
Showing results for 
Search instead for 
Did you mean: 

Passing mandatory parameter within several calculation view

Former Member
0 Kudos

Hi, I have syntax error when I try to pass parameter between several calculation view; let say:

Calculation view A: has mandatory parameter called company;

and

Calculation view B: has mandatory parameter called company;

What I want to achieve is to passing parameter from user from Calculation view B and then within Calculation view B, I called Calculation view A with user inputted parameter.

I have try similar like this, but giving me error:

var_out = SELECT ...

           FROM :CA_A a,

          "_SYS_BIC"."package-testing/CA_B" ('PLACEHOLDER' = ('$$COMPANY$$', COMPANY)) b

          ...

          ...

          ...;

SQL: sql syntax error: incorrect syntax near "COMPANY": line ...

NB: COMPANY is mandatory parameter inside Calculated view B

Accepted Solutions (1)

Accepted Solutions (1)

former_member184768
Active Contributor
0 Kudos

Hi,

With SPS 06, it is possible to pass on parameters from one Calc view to another Calc view in the script.

Please refer to section 6.5 Column View Parameter Binding in SQL script Reference guide.

The revised syntax for the PLACEHOLDER is as follows:

outtab = SELECT * FROM <CALC_VIEW> (PLACEHOLDER."$$client$$" => :in_client , PLACEHOLDER."$$currency$$" => :in_currency );

I tried it and it really really works ..

Regards,

Ravi

0 Kudos

Hi all,

is it possible join a calculation view with parameters with another table/model?

I mean - something like:

SELECT * FROM <calc_view> AS cv

INNER JOIN <another_table> AS table

ON cv.column1 = table.column1

WITH PARAMETERS ('placeholder' = ('$$my_param$$', table.column2)

Many thanks

BR

Massimiliano

former_member184768
Active Contributor
0 Kudos

Hi,

In my opinion, what you are looking for can be achieved with Variables. It need not be input parameter. Your requirement is like a WHERE condition for which Variables are more suited.

Regards,

Ravi

0 Kudos

Hi. I dont know how you construct your CV, but if let say you just retrieved all the data without any filter from input parameter, you can just simply join with your table.column2 using what join that suit you or even you can doing subquery.

Maybe you can define the problem more specifically?

Former Member
0 Kudos

Hello,

If you dont want to transfer all the data to the reporting view "where" clause is not a good idea. We have the same problem and want to restrict the data in the lowest view.

Is there any workaround for this? maybe using procedures?

Thanks,

Amir

former_member184768
Active Contributor
0 Kudos

Hi Amir,

The topic is deviating from the original post, but whether the restrictions happens at the lowest level or in the Calc engine depends upon the data model. In a simple case of Calc View, even the filters in the where clause are pushed down and the data set processed in Calc engine is reduced.

Regards,

Ravi

0 Kudos

Hi,

is this also possible with CE Functions? Is there a syntax for invoking CE_CALC_VIEW and supplying the input parameter value to achieve the same result as the SELECT statement provided by Ravi?

Thanks,

Peter

patrickbachmann
Active Contributor
0 Kudos

Ravi we are having this same problem in SPS05.  So this will definitely not work until we upgrade to SPS06?  I guess we will have to upgrade soon then if that's the case as we now have this exact need.

-Patrick

Former Member
0 Kudos

Hi Patrick,

In SP 63 it works for us.

Thanks,

Amir

patrickbachmann
Active Contributor
0 Kudos

Thanks Amir, we upgraded our dev box to SP67 and it now works!

-Patrick

justin_molenaur2
Contributor
0 Kudos

Old thread, same problem. This was the solution that worked for me.

Of interest, I could not use an input parameter of the same name in the parent CV as I was using the child CV. In your example you were able to use the same input paramter names.

For example, I had the two CV's, each had an input parameter ACCOUNT.

---This did not work, kept getting syntax errors no matter what I did

table =

SELECT COLUMNS

FROM "_SYS_BIC"."CHILD_CV"

(PLACEHOLDER."$$ACCOUNT$$"=>:ACCOUNT)

---This worked, changed the parent/calling CV input parameter name

table =

SELECT COLUMNS

FROM "_SYS_BIC"."CHILD_CV"

(PLACEHOLDER."$$ACCOUNT$$"=>:IN_ACCOUNT)

This was tested on a revision 82 box.

zeise
Advisor
Advisor
0 Kudos

Hi Justin,

I stumbled across the same problem. Could you find a way to get it working without renaming the variable "ACCOUNT"?

Best regards,

  Manuel

justin_molenaur2
Contributor
0 Kudos

Hi Manuel, I just ended up renaming the parameters actually, more of a workaround.

I think this is probably worthy of opening an OSS message to get confirmation from SAP if this is indeed a bug or it's the intended functionality.

Regards,

Justin

vanama1
Explorer
0 Kudos

Hi Ravi, the following code works, but want to pass two input parameters, encountering syntax error.

BEGIN
VAR_OUT =

CE_CALC_VIEW("_SYS_BIC"."xxx.playground.xxx/ACCOUNTINGDOCUMENTS_BASE",
    [ MANDT, BUKRS,BELNR,GJAHR,BVORG,BUZEI,KUNNR,LIFNR,MATNR,MAKTX,
     USNAM,KOART,DMBTR,WRBTR,PSWBT,PSWSL
    ] );

END

I used it as

BEGIN
VAR_OUT =

CE_CALC_VIEW("_SYS_BIC"."xxxx.playground.xxx/ACCOUNTINGDOCUMENTS_BASE",
    [ MANDT, BUKRS,BELNR,GJAHR,BVORG,BUZEI,KUNNR,LIFNR,MATNR,MAKTX,
     USNAM,KOART,DMBTR,WRBTR,PSWBT,PSWSL
    ], ' "BUKRS" =''0371'' ' );

END

how to pass input parameters or how to apply a WHERE clause for the output data set ?

thank you

justin_molenaur2
Contributor
0 Kudos

Why don't you consider not using CE functions and eliminating the complexity? Since you are just doing a straight VAR_OUT in a calculation view, instead use something like this which is widely used and understood.

VAR_OUT =

SELECT X FROM SYS_BIC

(PLACHOLDER statement)

WHERE CLAUSE;

This is much cleaner for me and I would see no advantage to using CE functions in this case, unless of course you're whole exercise is just to get this work with CE functions

Happy HANA,

Justin

pradeep_gupta
Active Participant
0 Kudos

Its the correct answer.

Have implemented the same as suggested in my CV.

Thanks a lot for sharing..!!

Former Member
0 Kudos

This message was moderated.

former_member210296
Participant
0 Kudos

This message was moderated.

Answers (8)

Answers (8)

former_member585626
Participant
0 Kudos

Hi,

I am able to call view in stored procedure...

FROM "_SYS_BIC"."......dcs.views/CV_STOCK_LIVE_VEHICLE"(PLACEHOLDER."$IN_AIRPORT_IATA$"=>:AIRPORT_IATA) WHERE .....

former_member210296
Participant
0 Kudos

This message was moderated.

Former Member
0 Kudos

This message was moderated.

Former Member
0 Kudos

Hi,

Try replacing "$$COMPANY$$" with ":COMPANY"

Hope it works...

Regards,

Papil

Former Member
0 Kudos

Hi, Rudy:

     If you found my solution works for you. Please kind close this thread.

    Thanks!

Tony

Former Member
0 Kudos

sorry, in my case, i have error when execute it, "Column store error".

It suppose this feature only work using graphical calculated view, but not sql based calculated view.

Former Member
0 Kudos

Hi, Tony:

Did you test the '[$COMPANY]' in your scripted calculation view? It seems not to work in my side. Any suggestion?

former_member182114
Active Contributor
0 Kudos

Hi guys,

This topic come back to forum from time to time and all the times the conclusion is doesn't work. I didn't tested Tony's solution myself  (will try when have a spare time).

Anyhow, there's a workaround if the both CA's are script based that is call it as procedure and select the result, like this:

Taking the original problem in mind (inside CA_A call CA_B)

1) add /proc to the end of name... and use :company (the parameter that exists on CA_A) and lt_company (or any other name to receive the result)

call "_SYS_BIC"."package-testing/CA_B/proc"(:company,lt_company);

2) use the returned data inside lt_company (which is already handled with :company sent on item 1)

SELECT ... FROM :lt_company;

Don't know if the optimizer can do the same work when calling the procedure than selecting the view.

Regards, Fernando Da Rós

henrique_pinto
Active Contributor
0 Kudos

Hi Rudy,

your request was not clear to me.

From what I understood, you have two existing calc views, CA_A and CA_B, that take the same input parameter COMPANY, and you want to do some kind of join with their outputs and also have the user input the company parameter only once, is that so??

If so, i suppose you can achieve that with a third calc view CA_C that takes COMPANY as a input param.

You could do something like:

lt_a = SELECT * FROM "_SYS_BIC"."package/CA_A" ( 'PLACEHOLDER' = ('$$COMPANY$$', :COMPANY) );

lt_b = SELECT * FROM "_SYS_BIC"."package/CA_B" ( 'PLACEHOLDER' = ('$$COMPANY$$', :COMPANY) );

var_out = CE_JOIN(:lt_a, :lt_b, ["DIMENSION_TO_JOIN"]);

Was this what you were looking for?

If not, let me know.

henrique_pinto
Active Contributor
0 Kudos

PS: this is not really optimal since it's mixing SQL & CE SQLScript...

The point is that, by the looks of it, the CE_CALC_VIEW() function doesn't support passing input parameters to it.

What you could do, if you wanted, was to use a SQL Join instead of CE_JOIN in the end.

Former Member
0 Kudos

Hi Pinto, thx for reply.

I think you misunderstood my problem, but I can explain again: I have 2 calculation view. CA_A and CA_B. Inside both have parameter called COMPANY. Now the real problem is to call (or SELECT) CA_A view from inside CA_B view which passing COMPANY parameter from CA_B to CA_A.

In depth, CA_A has script like doing MINUS from two tables which both table I filter by COMPANY input parameter. What I ask is how to CA_B calling CA_A with parameter and then doing some other MINUS and some aggregation.

Sorry for my broken english.

henrique_pinto
Active Contributor
0 Kudos

Got it.

Actually, passing the parameters from one CA to the other should be the easiest part.

Just do something like:

lt_a = SELECT * FROM "_SYS_BIC"."package/CA_A" ( 'PLACEHOLDER' = ('$$COMPANY$$', :COMPANY) );

Where "COMPANY" is the input parameter's name within both views.

Basically, within the calc view's code, just use colon & the parameter's name to refer to the paramete, similar to how you do with a logical table (e.g. :lt_a in the aforementioned case). It would also be the same to refer to a parameter within a procedure.

From your original code, the only difference is the colon you forgot before "COMPANY" parameter reference.

Within CA_A's own code, it should be the same to refer to the parameter within the code (i.e. :IP_COMP in the above case).

How to achieve what you're trying to is another story. But if you want help with the SQL itself, I'd ask it in another discussion thread.

Former Member
0 Kudos

Sorry, Pinto, but I think adding the :COMPANY giving me error too.

The error is:

Internal deployment of object failed;Repository: Encountered an error in repository runtime extension;Internal Error:Deploy Calculation View: SQL: sql syntax error: incorrect syntax near ":COMPANY": line 11 col 54 (at pos 777)

former_member184768
Active Contributor
0 Kudos

Hi Rudy,

You are right. Passing the variable is not allowed to be used in the parameter clause. The alternative I think would be to remove the input parameter from the calc view, joining view A and B on company code and passing the user input variable company code in the WHERE clause for the join..

something like:

SELECT ....

from CA_A a, CA_B b

where a.company = b.company

and a.company = :v_company;

Regards,

Ravi

henrique_pinto
Active Contributor
0 Kudos

Wow you're right, I had never tested a nested call of two calc views, but I had already successfully used a nested call of a Column View from within a calc view. I'd expect them to be identical in terms of syntax, that's why I thought it would work.

The strangest part is that even if you don't use any parameters from the "outer" calc and just do a nested call with a hardcoded parameter value, it will activate the calc but selecting on it fails with a syntax error. I.e. the same piece of code that works in SQL Editor, when called from within the context of a calc view, won't work.

Sounds like a bug to me.

Former Member
0 Kudos

Hi, Ravi:

     Have you ever tried your suggestion? I have a similar issue: CA_A is an analytic view,

company is the input parameter in CA_A.

      We cannot direclty invoke a.company as you mentioned above. You will hit error as follow

invalid column name: a.company: line 7 col 21 (at pos 345) at ptime/query/checker/check_expr.cc:604

       Thanks!

Tony

Former Member
0 Kudos

Hi, Folks:

    I have figured it out.

    1. You should use ('PLACEHOLDER' = ('$$COMPANY$$', COMPANY)) syntax .

    2. The correct format should be ('PLACEHOLDER' = ('$$COMPANY$$', '[$COMPANY]' ))

     I have tested it (validate it, activate it, execute it). It works fine.

     Cheers!

Tony

Former Member
0 Kudos

In my latest find out, I didnt think it is possible in current HANA. Maybe HANA will evolve and support this kind of feature later.

former_member184768
Active Contributor
0 Kudos

Hi Rudy,

Couple of points:

1) Did you try running the statement directly in SQL editor like

SELECT ...

           FROM "_SYS_BIC"."package-testing/CA_B" ('PLACEHOLDER' = ('$$COMPANY$$', COMPANY)) b

2) Can you please change the case of $$COMPANY$$ to lowercase like $$company$$.

Regards,

Ravi

Former Member
0 Kudos

Hi Ravindra,

1. I tried the SQL within SQL editor (of course I replace the parameter into something else) and it was runnable.

2. I already try the lower case and upper case version, both giving the same error.