Skip to Content

HANA studio Store Procedure to look up a table

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Posted on Nov 01, 2019 at 03:20 AM

    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:


    capture.jpg (30.0 kB)
    Add a comment
    10|10000 characters needed characters exceeded

    • 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

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.