cancel
Showing results for 
Search instead for 
Did you mean: 

Pass parameter from calculation view to projection

Former Member
0 Kudos
258

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

Accepted Solutions (0)

Answers (1)

Answers (1)

lbreddemann
Active Contributor
0 Kudos

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
0 Kudos

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.