08-18-2016 9:57 AM
Hi Experts,
I want to write a AMDP procedure by passing an internal table as where condition.
What would be the best approach? Our system is NW 7.4. I have used APPLY FILTER function but it didn't work.
My input internal table looks like this:
Consumer | Date Low | Date High |
---|---|---|
1001 | 01.01.2015 | 31.12.2016 |
1002 | 01.01.2014 | 30.06.2014 |
Table in HANA DB
Consumer | Date | Sales |
---|---|---|
1001 | 01.01.2014 | 10 |
1001 | 01.01.2015 | 10 |
1001 | 18.08.2016 | 10 |
1002 | 01.01.2014 | 10 |
1002 | 02.02.2014 | 10 |
1002 | 30.06.2014 | 10 |
1002 | 01.01.2016 | 10 |
Output internal Table
Consumer | Sum of Sales |
---|---|
1001 | 20 |
1002 | 30 |
Looping my internal table and getting the sum for each record will work but as you know it is performance killer.
I would be calling this procedure in a BW transformation where data come in package of 50000 records(so my input internal table can contain 50,000 records at a time) .
08-19-2016 11:49 PM
Hello Sumukan,
you can solve this with a join between the input parameter table and the database table. E.g.
lt_res = select t2.consumer, sum(t2.sales)
from :it_input as t1 inner join "<schema>"."<table>" as t2
on t1.consumer = t2.consumer
and t1.datelow <= t2.date
and t1.datehigh >= t2.date
group by t2.consumer;
Consider that you have to replace the input parameter, schema/table name and column names with your values (cause I don't know it).
Best Regards,
Florian