1

HANA Version: SP12

All,

I've successfully created Calc Views with INPUT_PARAMETERS as described by Lars in many blogs and forums. While these views work without issue when querying directly for single and multi inputs, 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);

Question raised on SAP SCN. Located here!

Dakota H.
  • 11
  • 3

1 Answers1

0

Did you tried to use WHERE clause instead of PLACEHOLDER?

base_calc =

select

PLANT,
MATERIAL,
MATERIAL_BU,
etc....

from "_SYS_BIC"."CA_EXAMPLE_PRODUCTIVITY"

WHERE MATERIAL = var_MATERIAL AND PLANT = var_PLANT;
Berkay Uğur Eminol
  • 1