Skip to Content
0
Jul 06, 2018 at 12:39 PM

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

528 Views

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);