Search code examples
hanahana-sql-scripthana-studio

Error while selecting the calculation view through variables

I am facing one interesting issue while reading from a calculation view through variables ... the code is as follows:

do begin
declare lv_ww nvarchar(6);
declare lv_quarter nvarchar(6);

select "WORKWEEK","QUARTER" INTO lv_ww,lv_quarter from "ABC"."TABLE1";

select count(*) from "_SYS_BIC"."CID" (PLACEHOLDER."IP_SNAPSHOTWW" => :lv_ww,PLACEHOLDER."IP_QUARTER" => :lv_quarter);
end;

I am getting column store error , search table error , plan operator failed in the select count(*) line. so basically out of memory issue because it is taking more than 15 gb memory.

Now when i will hard code the values for lv_ww = '202114' and lv_quarter = '2021Q2'

do begin
declare lv_ww nvarchar(6) default '202114';
declare lv_quarter nvarchar(6) default '2021Q2';

select count(*) from "_SYS_BIC"."CID" (PLACEHOLDER."IP_SNAPSHOTWW" => :lv_ww,PLACEHOLDER."IP_QUARTER" => :lv_quarter);
end;

It runs perfectly fine and takes only 0.012 gb.

Note - lv_ww and lv_quarter while calculating from TABLE1 are perfectly fine and giving us values as (lv_ww = 202114 and lv_quarter = 2021Q2)

Kindly let me know if you need any other details and please enlighten me for any solutions.

Solution

  • Same answer as I gave here :

    Aright, I'd go out on a limb here and say: the different execution time/memory is probably due to different execution plans. And since the difference between the statements is that the value of the selection is NOT KNOWN at query compilation time in the first case, but IS KNOWN in the other case, that's likely the cause for the different execution plans.

    You may want to check this theory!

    One way to address this could be to use the BIND_AS_VALUE SQLScript function (https://help.sap.com/viewer/de2486ee947e43e684d39702027f8a94/2.0.05/en-US/0b2958ee0426496f9c084c92b14993f1.html)