HANA studio Store Procedure to look up a table

[ms_k]

Hello All,

I am looking for a store procedure SQL code in HANA studio to look up a table which has % range and pick the specific value.

The requirement is:

I have a transaction table with COL1, COL2 and Percentage with values A1, B1 and 97%. I have a master table with COL1,COL2,Low%,High%,value with values in below table with % range.

COL1 COL2 Low% High% Value

A1 B1 90 95 1

A1 B1 96 100 2

A1 B1 100 200 3

I need a SQL procedure to take the transaction entry(A1, B1 & 97%) as input and look up the master table with COL1 & COL2 as primary keys to check under which range does 97 % fall into and select the Value(2) against it as result.

Please help.

Regards,

MSK

[former_member631142]

Hi MSK,

You can refer following SQL code in HANA procedure to get value from lookup table based on range %.

DO BEGIN
IT_TRANSACTION = SELECT 'A1' AS COL1, 'B1' AS COL2, 97 AS PERCENTAGE_VAL FROM DUMMY
                  UNION ALL
                 SELECT 'A1' AS COL1, 'B1' AS COL2, 91 AS PERCENTAGE_VAL FROM DUMMY;

IT_LOOKUP = SELECT 'A1' AS COL1, 'B1' AS COL2, 90 AS LOW_VAL, 95 AS HIGH_VAL, 1 
             AS VALUE2 FROM DUMMY 
            UNION ALL
      SELECT 'A1' AS COL1, 'B1' AS COL2, 96 AS LOW_VAL, 100 AS HIGH_VAL, 2 AS VALUE2  
      FROM DUMMY
      UNION ALL
     SELECT 'A1' AS COL1, 'B1' AS COL2, 100 AS LOW_VAL, 200 AS HIGH_VAL, 3 AS VALUE2  
      FROM DUMMY;

IT_RESULT = SELECT T1.COL1, T1.COL2, T1.PERCENTAGE_VAL, T2.VALUE2
           FROM :IT_TRANSACTION AS T1
          INNER JOIN :IT_LOOKUP AS T2
          ON T1.COL1 = T2.COL1 AND T1.COL2 = T2.COL2
          AND T1.PERCENTAGE_VAL >= T2.LOW_VAL AND T1.PERCENTAGE_VAL <= T2.HIGH_VAL;

SELECT * FROM :IT_RESULT;
END

Output of the statement is as below:

[ms_k]

Hello Kedar,

Thanks for the code.

When I say Transaction, it is a calculation view with star join.So how do I include a calculation view in the stored procedure to get the columns in it. I have to take the value from Look up table and pass the result to a new column(Discount) in the calculation view.

I cannot use the calculation view in the Stored procedure.

Any clues please?

Regards,

MSK

[former_member631142]

Hi MSK,
If I understand your comments correctly, please refer below:

You can call calculation view within stored procedure. In the calculation view, you can do following steps:
i) In projection node, add lookup table.
ii) Join this node with your main transaction table in the join node of calculation based on Col1 and Col2 join 
columns.
iii) Add projection node on top of join node and apply filter condition to filter ouut records based on low and high value % of lookup table.
iv) It will give you desired output.

Regards
Kedar