Calling parameterized Cal View, from HANA Stored Procedure

[former_member187794]

Hi All

I have a requirement to call a parameterized view from a Stored Procedure. This is the first time that, I am writing a stored procedure.

Hence need your help, to get me the syntax.

This was my sql script (before converting the view to parameterized)

OUT_TT_INVENTORY = SELECT "Material" as "material", "Location" as "location", "InventoryCategory" as "inventoryCategory",

  "Value" as "value" FROM "_SYS_BIC"."getInventory" p, :IN_MATERIAL m, :IN_LOCATION l

  WHERE LOWER(p."Material") = LOWER(LPAD(m."paramValue", 18, 0)) AND LOWER(p."Location") = LOWER(LPAD(l."paramValue", 10, '0'))

  ORDER BY "material", "location", "inventoryCategory";

Can someone suggest me how to modify this SQL script? (to include PLACEHOLDER for getInventory view, in the above script)

I am on SPS10

Thanks for your replies

Regards

Giri

[former_member187794 · Accepted solution]

i have used this statement.

SELECT "Material", "Location", 'Available' as "Category", "AvailableVal" as "value"

  FROM  "_SYS_BIC"."Sandbox..v5/getIntegrationCalc"

  (PLACEHOLDER."$$i_matnr$$"  => :v_MTRLS_STRING, PLACEHOLDER."$$i_location$$" => :v_LOCS_STRING, PLACEHOLDER."$$i_usr$$" => :v_USR);

And used some StringAGG function to build the exact string to pass onto Place Holder params

Regards

Giri

[Former Member]

Hi Giri,

I believe now you have to split the query into 2 parts :

1. First part would contain bring the data from parameterized view into your local TT

Eg:

TT_GET_INVENTORY = SELECT * FROM "_SYS_BIC"."getInventory"(PLACEHOLDER."$$inventory_category$$" => :inventory_category);

I have assumed that you need to select all the columns(thats why select *) and you want to filter data on inventory_category.

2. Use the above TT in the same query you had given .

OUT_TT_INVENTORY = SELECT "Material" as "material", "Location" as "location", "InventoryCategory" as "inventoryCategory",  "Value" as "value" FROM :TT_GET_INVENTORY p, :IN_MATERIAL m, :IN_LOCATION l   WHERE LOWER(p."Material") = LOWER(LPAD(m."paramValue", 18, 0)) AND LOWER(p."Location") = LOWER(LPAD(l."paramValue", 10, '0'))  ORDER BY "material", "location", "inventoryCategory";

Rest everything would be same.

Regards,

Anil

[former_member187794]

Thanks Anil.

I have tried this. But the problem is, the input is a table type. So, I am not understanding how to pass the values from the table type to the place holder.

Regards

Giri

[Former Member]

Hi Giri,

I believe that we cannot create calculation view to accept table type as input parameter.

It can only be scalar type I guess and you can have multiple values.

In case of multiple values, you can change the select statement accordingly as

select ... from CV1( 'PLACEHOLDER' = ('$$var$$' = 'VAL1,VAL2,VAL3')

So in your case. (procedure having table type as input parameter). I believe you need to create intermediate variables to accept data from input table type parameter and put into these variables and pass these variables as above to fetch required data from CV as above.

Regards,

Anil