HANA Calc View Place Holder usage when joining in Table Function or Stored Procedure

[former_member587084]

HANA Version: SP12

All,

I've successfully created Calc Views with INPUT_PARAMETERS as described by Lars on many occasions but have run into a bit of a hitch with pushing complete filters to PLACEHOLDERs within CTEs/temp tables of SQL script . While these views work without issue when querying directly for single and multi inputs less than 5000 characters, I'm encountering an issue with performing joins on the Calc View itself within a stored proc or table function..

Example:

"BASE_SCHEMA"."BASE_TABLE_EXAMPLE" - record count(*) ~ 2million records

• Keys: Material (20k distinct), Plant (200 distinct)

"_SYS_BIC"."CA_EXAMPLE_PRODUCTIVITY"

• Input Parameters: IP_MATNR (nvarchar (5000)), IP_PLANT (nvarchar(5000))

Issue #1: The maximum value for nvarchar is 5000. Unable to utilize multiple inputs within the parameter if the count of distinct characters are 5000+.

Issue #2: How to use PLACEHOLDER logic in the same method of performing an INNER_JOIN in SQL.

base_data = 

  select 
  PLANT
 ,MATERIAL

 from "BASE_SCHEMA"."BASE_TABLE_EXAMPLE"
 group by PLANT,MATERIAL; 

I would think to perform the below but the output would cause issues when concatenating multiple strings for use within input parameter of nvarchar(5000).

select

string_agg(PLANT,''',''') as PLANT
,string_agg(MATERIAL,''',''') as MATERIAL
into var_PLANT, var_MATERIAL

from
(
   select

   PLANT
   ,MATERIAL

   from :base_data
);

While I'm successful up to this point, once adding the variables into the PLACEHOLDER of the Calc View, it fails stating that I'm passing too many characters to the IP. Any suggestions??? Thanks in advance.

base_calc =

  select

  PLANT
  ,MATERIAL
  ,MATERIAL_BU
  ,etc....

  from "_SYS_BIC"."CA_EXAMPLE_PRODUCTIVITY"
  (PLACEHOLDER."IP_MATNR"=> :var_MATERIAL,  --<---Fails here. :(
   PLACEHOLDER."IP_PLANT"=> :var_PLANT);

[SergioG_TX]

HI Dakota,

have you thought about passing a table type as an input param? that way you can have a list of values rather than a concatenated string? it may work better so you are not limited to how many you need to pass. once you have the table type values in your view... then you could try joining - would this be a scenario that fits your req?

cheers,

SG

[former_member587084]

Thanks for your reply Mr. Guerrero,

I honestly did not think to pass a table type. I will definitely look into it and reply back with my results. Thank you sir.