aggregate select on combination of several fields

143 Views

Hi All,

Today I got a requirement to display stock aging data for any previous period and I decided to use table CKMLHD&CKMLPP. But I then got a problem on selecting data from the large table CKMLPP. In this table we have some data like belows:

KALNR        BDATJ   POPER      LBKUM
100515932     2009    012          22.000
100515932     2010    001          12.000
100931044     2010    001          1.000
100931044     2010    002          1.000
...

Now we need to get stock data (field CKMLPP-LBKUM) from the latest period(CKMLPP-BDATJ + CKMLPP-POPER). For here we want to get the following records:

KALNR        BDATJ   POPER      LBKUM
100515932     2010    001          12.000
100931044     2010    002          1.000
...

Considering the table size (more than 4 million of lines), we don't want to select all of them into internal tables and then compare each other to get the latest data. I tends to use some aggregate expressions like max( CKMLPP-BDATJ + CKMLPP-POPER ) to minimize the workload, but unfortunately, the expression max() does not support column combination, it can only select max( CKMLPP-BDATJ ) or max( CKMLPP-POPER ), but that's meanless.

I know normally for material ledge records, it should have stock data in one line per period in CKMLPP even there is no GR/GI during the specified period, but for some reason, lots of period data not exist so we can not just use sql like select LBKUM from CKMLPP where BDATJ = p_period0(4) and POPER = p_period4(3).

So could any of you give me an idea how to handle this situation efficiently?

Thank you very much!

Best Regards,

Jeff