CE Function : CE_CALC_VIEW - running with placeholders

[sreehari_vpillai]

Hi Experts ,

I am bit stuck in specifying placeholders in a graphical calculation view.

I created one graphical calcualtion view which has 2 mandatory input paramters(PLANT & DATE).

• How use CE Function to access the cal view with placeholders ?
• how can i use direct select statement to fetch data from calculation view with dynamic placeholders (I am trying to fetch calculation view inside a procedure)

SELECT  "YYYYMM", 
      "DebitCreditCode",
sum("AmountInCompanyCodeCurrency") AS "Amount" FROM "_SYS_BIC"."TurnOverValue_BugFix/ABCDEFG_L1"
('PLACEHOLDER' = ('$$PLANT$$', '0001'), 'PLACEHOLDER' = ('$$TO_DATE$$', '201401')) 
GROUP BY 
"DebitCreditCode", 
"YYYYMM";

Here, i need to replace '0001' and '201401' with dynamic values. I tried bot ":variable_name' and "variable_name". Both raise error.

Thanks in advance ,

Sreehari V Pillai

[sreehari_vpillai · Accepted solution]

any hope ????

[former_member182302 · Accepted solution]

Hi Sreehari,

Have a look at this thread:

As Jody referred , may be parameter binding is not supported by CE functions.

You can use Graphical Calculation view and use Map Input Parameters dialog .

So instead of a script based view, create a graphical calculation view and call it from your procedure

On a side note have a question, Why are you having an additional layer on your analytic view? Is it only for having a different Group by clause?

Regards,

Krishna Tangudu

[former_member182302 · Accepted solution]

HI ,

Have a look on this thread:

Regards,

Krishna Tangudu

[sreehari_vpillai · Accepted solution]

Oh I forgot to close the thread . From ur previous reply , I got the issue solved .. thanks for the new URL btw

[Former Member]

Hi Sreehari,

why you do not create the calculation view in script mode. There you can write SQL:

SELECT  
     "YYYYMM",  
     "DebitCreditCode",  
     sum("AmountInCompanyCodeCurrency") AS "Amount" 
FROM "_SYS_BIC".TurnOverValue
WHERE "PLANT"        = :plant
AND        "TO_DATE"  =  :date;

Here you only have to create the direct Input Parameters plant and date with corresponding types.

Is it that, you wanted to know?

Regards

Jonas

[sreehari_vpillai]

Hi Jonas,

How ever we are blessed with CE functions to access the calculation view. I was confused primarily about the generic syntax of setting place-holders in CE functions while fetching calculated view .

I can not pass the plant or date as you mentioned in your query as calculation view does not project those columns in the output data.

(I have many calc views with place-holders with me, those I got to use in a procedure to do some sort of manipulations . The same procedure I will consume from my abap stack. I am still able to consume calculation views from abap with place-holders, using native sql. )

Regards

Sreehari V

[former_member184768]

Hi Sreehari,

For the placeholder syntax, can you please try the following:

  FROM "_SYS_BIC"."myPackage/MY_CALC_VIEW"

(

   PLACEHOLDER."$$INPUT_PARAM_1$$" => :v_value_1,

   PLACEHOLDER."$$INPUT_PARAM_2$$" => :v_value_2

)

Regards,

Ravi

[sreehari_vpillai]

Hi Ravindra,

From your  replies to others questions in SDN , i found the sysntax even before, but still facing issues

Calculation view -- SQL Generated automatically

Generated SQL: 
SELECT TOP 200 "DebitCreditCode", "YYYYMM",
sum("AmountInCompanyCodeCurrency") AS "AmountInCompanyCodeCurrency"
FROM "_SYS_BIC"."TurnOverValue_BugFix/INVENTORY_TURNOVER_L1"
('PLACEHOLDER' = ('$$PLANT$$', '0001'), 'PLACEHOLDER' = ('$$TODATE$$', '201401'))
GROUP BY "DebitCreditCode", "YYYYMM"

The same is successfully executed and got the output too.

Procedure ( Got activated successfully )

But when i am trying to execute the procedure from SQL Console, i am getting the following error.

call  "_SYS_BIC"."TurnOverValue_BugFix/GE_INVENTORY_DATA" ('201401','0001',?);

Statement 'call "_SYS_BIC"."TurnOverValue_BugFix/GE_INVENTORY_DATA" ('201401','0001',?)' 


successfully prepared





　

Could not execute 'call "_SYS_BIC"."TurnOverValue_BugFix/GE_INVENTORY_DATA" ('201401','0001',?)' in 246 ms 203 µs . 


SAP DBTech JDBC: [2048]: column store error:  [2048] column store error: search table error:  [34092] search on calculation model requires parameters;Required variable $$TODATE$$ is not set.Please check lines: 5, 

I am totally confused, and not able to figure out the problem.

Thanks in advance

Sreehari