Problem with Aggregate function

[satish_kumar127]

Hi experts,

i have a table as

counter       value      operation

1                   100          40

1                    100         60

2                     200        60

from the above table , i want to fetch the data based on the max(counter) for the same operation.

i want my data like

counter       value      operation

   1                100          40

   2                 200         60

i have writtern my query as follows

select * from afpo into table it_afpo   WHERE matnr IN s_matnr

                                                                AND dwerk IN s_werks

                                                                 AND ltrmi IN s_ltrmi

if it_afpo is not initial.

SELECT aufnr isdd isdz iedd iedz vornr ersda max( rmzhl) FROM afru

                                                                                            INTO CORRESPONDING FIELDS OF TABLE it_afru

                                                                                            FOR ALL ENTRIES IN it_afpo

                                                                                           WHERE aufnr = it_afpo-aufnr

                                                                                            AND vornr IN ('0040' , '0060') group by aufnr isdd isdz iedd iedz vornr ersda.

endif.

but problem is there with For All Entries statement.

without using for all entries  , how to achieve this?.

would be grateful if somebody helps.

thanks in advance

regards

satish

[RaymondGiuseppi]

Use a JOIN option, I would also use AFKO table in the JOIN.

SELECT afru~aufnr afru~isdd afru~isdz afru~iedd afru~iedz afru~vornr afru~ersda
        MAX( afru~rmzhl ) AS rmzhl
   INTO CORRESPONDING FIELDS OF TABLE it_afru
   FROM afru
   JOIN afko
     ON afko~rueck = afru~rueck
   JOIN afpo
     ON afpo~aufnr = afko~aufnr
   WHERE afpo~matnr IN s_matnr
     AND afpo~dwerk IN s_werks
     AND afpo~ltrmi IN s_ltrmi
     AND afru~vornr IN ('0040', '0060')
   GROUP BY afru~aufnr afru~isdd afru~isdz afru~iedd afru~iedz afru~vornr ersda.

Regards,

Raymond

[satish_kumar127]

hi raymond,

thanks for the prompt reply. i did the same thing which you have mentioned above. but it's picking all the records instead of max value record.

the following data is from my internal table after execting the mentioned query.

regards

satish

[RaymondGiuseppi]

My code was (trying to be) equivalent to your FOR ALL ENTRIES except the implicit deletion of duplicates. For best result you have to use a aubquery like

SELECT afru~aufnr afru~isdd afru~isdz afru~iedd afru~iedz afru~vornr afru~ersda afru~rmzhl
   INTO CORRESPONDING FIELDS OF TABLE it_afru
   FROM afru
   JOIN afko
     ON afko~rueck = afru~rueck
   JOIN afpo
     ON afpo~aufnr = afko~aufnr
   WHERE afpo~matnr IN s_matnr
     AND afpo~dwerk IN s_werks
     AND afpo~ltrmi IN s_ltrmi
     AND afru~vornr IN ('0040', '0060')
     AND afru~rmzhl EQ ( SELECT MAX( rmzhl ) FROM afru WHERE rueck = afru~rueck ).

The problem could be in your GROUP clause which has too many fields, preventing efficient grouping. (Be more precise on the required grouping)

>    As in the where clause of subquery you may add a AND vornr = afru~vornr to keep one record per operation, else one record per order.

Regards,

Raymond

[satish_kumar127]

hi raymond,

thanks for the useful kt regarding the issue. your code is absolutely working fine but as you mentioned GROUP clause is having too many fields causing some inconsistency.

once again thanks a lot for the useful input.

Regards

satish

[PeterJonker]

What exactly is the issue with the FOR ALL ENTRIES statement ?

If you want to have the entries as you suggested you can sort theinteral table by operation counter DESCENDING. Then use DELETE ADJACENT DUPLICATES from ITAB comparing operation and you will be left with the max counter per operation.

[satish_kumar127]

hi peter,

thanks for the valuable input. my issue has been resolved with your input.

regards

satish

[buddhika_krishantha]

Use Collect keyword.