on 10-01-2012 12:28 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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?
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
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.
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
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
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 .....
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
This message was moderated.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
This message was moderated.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
Try replacing "$$COMPANY$$" with ":COMPANY"
Hope it works...
Regards,
Papil
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi, Rudy:
If you found my solution works for you. Please kind close this thread.
Thanks!
Tony
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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.
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)
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
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.
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
87 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.