Input parameter and Result View

[Former Member]

Hello everyone,

I am having trouble with using a result view from within a procedure.

The definition of the result view looks like this:

create procedure NEO_CCG502K10CQSC8NX8DNT6J7MA.GET_RULE_050_PROC_V1(
    in i_tenant_id NVARCHAR,
    out o_result NEO_CCG502K10CQSC8NX8DNT6J7MA.RULE_050_TYPE)
    reads sql data
    with result view NEO_CCG502K10CQSC8NX8DNT6J7MA.RULE_050_PROC_VIEW
    as ... 

I can call this result view from the SQL console the following without any problems:

select * from NEO_CCG502K10CQSC8NX8DNT6J7MA.RULE_050_PROC_VIEW WITH PARAMETERS ('placeholder' = ('$$i_tenant_id$$', '''0001'''));

Next step is, I try to call this from within a (different) SQL-procedure. I declare the following:

...

declare cursor c_cursor for select * from NEO_CCG502K10CQSC8NX8DNT6J7MA.RULE_050_PROC_VIEW WITH PARAMETERS ('placeholder' = ('$$i_tenant_id$$', '''0001'''));
...

The procedure is compilable, and I can create it, but once I execute it, I get the following error:

Could not execute 'call NEO_CCG502K10CQSC8NX8DNT6J7MA.EVALUATE_RULES_050_PROC_V1( '0001', '0001', o_result =>? )' in 475 ms 994 µs . 
SAP DBTech JDBC: [257]: sql syntax error:  [257] "NEO_CCG502K10CQSC8NX8DNT6J7MA"."EVALUATE_RULES_050_PROC_V1": line 23 col 2 (at pos 873): [257] (range 3) sql syntax error exception: sql syntax error: incorrect syntax near "0001": line 1 col 126 (at pos 126) 

I then tried to play around with the Input Variable, for instance this

select * from NEO_CCG502K10CQSC8NX8DNT6J7MA.RULE_050_PROC_VIEW WITH PARAMETERS ('placeholder' = ('$$i_tenant_id$$', '0001'));

works as well, once I call it directly from the SQL console, but once I use exactly this SQL syntax in my procedure,

then it gives me the following error:

Could not execute 'call NEO_CCG502K10CQSC8NX8DNT6J7MA.EVALUATE_RULES_050_PROC_V1( '0001', '0001', o_result =>? )' in 304 ms 184 µs . 
SAP DBTech JDBC: [1301]: numeric or value error:  [1301] "NEO_CCG502K10CQSC8NX8DNT6J7MA"."EVALUATE_RULES_050_PROC_V1": line 25 col 3 (at pos 946): [1301] (range 3) numeric or value error exception 

Once to try to call the result view from my SQL console like this:

select * from NEO_CCG502K10CQSC8NX8DNT6J7MA.RULE_050_PROC_VIEW WITH PARAMETERS ('placeholder' = ('$$i_tenant_id$$', '"0001"'));

then I get the following error:

Could not execute 'select * from NEO_CCG502K10CQSC8NX8DNT6J7MA.RULE_050_PROC_VIEW WITH PARAMETERS ('placeholder' = ...' in 282 ms 621 µs . 
SAP DBTech JDBC: [2048]: column store error: search table error:  [34023] Internal error during instantiating calculation model 

What am I missing? Can anyone give me some advice?

[Former Member · Accepted solution]

In the meantime, I figured out, that the call with '0001' was the correct one,

and that the error message I received was related to a different part of the SQL procedure.

Now I am trying to use the Input Parameter of the SQL procedure to call the result view as follows:

create procedure NEO_CCG502K10CQSC8NX8DNT6J7MA.CALL_RULE_050_PROC_VIEW(
  in i_tenant_id NVARCHAR,
  out o_result NEO_CCG502K10CQSC8NX8DNT6J7MA.RULE_050_PROC_VIEW
  ) reads sql data
  as
begin
  o_result = select * from NEO_CCG502K10CQSC8NX8DNT6J7MA.RULE_050_PROC_VIEW WITH PARAMETERS ('placeholder' = ('$$i_tenant_id$$', ':i_tenant_id'));
end

Once I call this procedure above, then I get the following error:

Could not execute 'call NEO_CCG502K10CQSC8NX8DNT6J7MA.CALL_RULE_050_PROC_VIEW( '0001', o_result =>? )' in 288 ms 629 µs . 
SAP DBTech JDBC: [2048]: column store error:  [2048] column store error: search table error:  [34023] Internal error during instantiating calculation modelPlease check lines: 7, 

Does anyone know how to pass an Input Parameter into the Result View?

[Former Member · Accepted solution]

The following thread http://scn.sap.com/thread/3159579 touches the same topic,

but does not give an answer for how to call a result view with an input parameter.

Since I have further logic in the calling procedure, I can't get around using the :i_tenant_id parameter.

Can anyone confirm, if this is possible of not?

[lbreddemann · Accepted solution]

Hi Rolf,

to be honest I have difficulties following your topic here.

As the documentation states, you can use the PLACEHOLDER.<parameter name> syntax to provide parameters to a result view.

Maybe this example gets you a step further:

create procedure funny_data (IN filter NVARCHAR, 
                             OUT o_result TABLE (BLA NVARCHAR(5), BLUPP NVARCHAR(30), BLIPP INTEGER) 
                             )


language SQLSCRIPT

READS SQL DATA 
WITH RESULT VIEW rv_funny_data
AS
BEGIN


    o_result = SELECT MAP(TO_INTEGER (RAND()*2),
                           1,   CAST (:filter as NVARCHAR(5)),
                           2, 'BBBBB', 'XXXXX'  ) as BLA,
                      u1.USER_NAME AS BLUPP,
                      TO_INTEGER ( RAND()*100) AS BLIPP
               FROM USERS u1 cross join USERS u2;
END;




call funny_data ('001', ?)


select * from rv_funny_data (PLACEHOLDER."$$filter$$"=>'001')

Now encapsulate this into yet another procedure...

drop procedure call_funny;
create procedure call_funny (IN filter NVARCHAR, 
                             OUT o_result TABLE (BLA NVARCHAR(5), BLUPP NVARCHAR(30), BLIPP INTEGER) 
                             )
language SQLSCRIPT                          
READS SQL DATA 
as
BEGIN
DECLARE nfilter NVARCHAR(10);


    select filter||'FX' into nfilter from dummy;
    o_result = select * from rv_funny_data (PLACEHOLDER."$$filter$$"=> :nfilter);




END;


call call_funny ('010', ?)

Seems to work for me...

- Lars

[Former Member · Accepted solution]

Hello Lars,

Thanks for your reply. I tried this:

drop procedure NEO_CCG502K10CQSC8NX8DNT6J7MA.CALL_RULE_050_PROC_VIEW;
create procedure NEO_CCG502K10CQSC8NX8DNT6J7MA.CALL_RULE_050_PROC_VIEW(
  in i_tenant_id NVARCHAR,
  out o_result NEO_CCG502K10CQSC8NX8DNT6J7MA.RULE_050_PROC_VIEW
  ) reads sql data
  as
begin
  --- o_result = select * from NEO_CCG502K10CQSC8NX8DNT6J7MA.RULE_050_PROC_VIEW WITH PARAMETERS ('placeholder' = ('$$i_tenant_id$$', :i_tenant_id ));
  o_result = select * from NEO_CCG502K10CQSC8NX8DNT6J7MA.RULE_050_PROC_VIEW (PLACEHOLDER."$$i_tenant_id$$"=> :i_tenant_id); 
end

and now, it works!

Can you (or anyone else) explain, what the difference between

     WITH PARAMETERS ('placeholder' = ('$$i_tenant_id$$', :i_tenant_id ))

and

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

is?

With the first approach, the procedure is not compilable, I get the following SQL syntax error:

SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near ":i_tenant_id": line 7 col 129 (at pos 328)