Pass parameter from calculation view to projection

[Former Member]

Hi,

The scenario I'm working on is the following:

I have created a SQL script calculation view that has an input parameter that is used to retrieve data - this SQL script is functioning well based on the value entered in the parameter

Now this SQL script calculation view is to be used in a graphical calculation view because the result sets need to be joined. However, I need to be able to enter the parameter value in the graphical calcualtion view and pass it down to the SQL script calcuation view. The parameter is not used to filter on a column, but is used to determine a 'decision path' in the SQL script in order to dynamically retrieve the correct values based on the parameter.

Any ideas on how this can be achieved? When I add the same parameter to the graphical view, it is not passed down to the SQL script calculation view.

Kr,

Tom

[lbreddemann]

Too little information.

Show us your models.

Show us the parameter mapping.

Show us how the parameter is not passed through.

Don't forget to mention your HANA revision.

- Lars

[Former Member]

Hi,

Calculation view:

 
 LT_KEKO_MATNR = SELECT DISTINCT MANDT, MATNR


 
                  FROM "SLT_PRD"."KEKO";


 
 


 
 LT_KADKY_MAX = SELECT MANDT, MATNR, max(KADKY) as KADKY


 
                FROM "SLT_PRD"."KEKO"


 
                GROUP BY MANDT, MATNR;


 
                


 
 LT_KADKY_P_CDAT_MAX = SELECT MANDT, MATNR, max(KADKY) as KADKY


 
                       FROM "SLT_PRD"."KEKO"


 
                       WHERE KADKY <= :P_CDAT


 
                       GROUP BY MANDT, MATNR;


 
                       


 
 LT_KADKY_P_CDAT_MIN = SELECT MANDT, MATNR, min(KADKY) as KADKY


 
                       FROM "SLT_PRD"."KEKO"


 
                       WHERE KADKY > :P_CDAT


 
                       GROUP BY MANDT, MATNR;



/* Determine the KADKY (date) value to retrieve the required KALNR in the next step
   If no parameter is entered for P_CDAT (default value '00000000') then use LT_KADKY_MAX
   If a parameter is entered, use the KADKY from LT_KADKY_P_CDAT_MAX - if not found then use
   KADKY from LT_KADKY_P_CDAT_MIN*/



 
                       


 
 LT_KEKO_KADKY = SELECT A.MANDT, A.MATNR, 


 
                 (CASE WHEN :P_CDAT = '00000000' THEN B.KADKY WHEN C.KADKY IS NOT NULL THEN C.KADKY ELSE D.KADKY END) as KADKY


       
             FROM :LT_KEKO_MATNR as A


 
                 LEFT JOIN :LT_KADKY_MAX as B


 
                 ON A.MANDT = B.MANDT


 
                 AND A.MATNR = B.MATNR


 
                 LEFT JOIN :LT_KADKY_P_CDAT_MAX as C


 
                 ON A.MANDT = C.MANDT


 
                 AND A.MATNR = C.MATNR                      


 
                 LEFT JOIN :LT_KADKY_P_CDAT_MIN as D


 
                 ON A.MANDT = D.MANDT


 
                 AND A.MATNR = D.MATNR;  

The SQL script has additional data that is being fetched, but in short it results in a table with fields:

MANDT, MATNR, KADKY - this table holds a unique KADKY value for each MANDT - MATNR

Now in my graphical calculation view I want to add the KADKY value to the source projection which contains amongst others MANDT and MATNR, so by means of a left join on MANDT and MATNR extend it with the KADKY value. The SQL script calculation view is included in the graphical view by means of a projetion node.

Since the KADKY value is determined dynamically based on a user input date (paramater) I need a way to pass a value from the graphical calculation view through the projection node that contains the SQL script calculation view.