topic Problem with Aggregate function in Application Development and Automation Discussions

Problem with Aggregate function

satish_kumar127 — Mon, 22 Sep 2014 07:22:02 GMT

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

Re: Problem with Aggregate function

RaymondGiuseppi — Mon, 22 Sep 2014 07:56:15 GMT

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

Re: Problem with Aggregate function

PeterJonker — Mon, 22 Sep 2014 07:58:06 GMT

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.

Re: Problem with Aggregate function

buddhika_krishantha — Mon, 22 Sep 2014 08:09:33 GMT

Use Collect keyword.

Re: Problem with Aggregate function

satish_kumar127 — Mon, 22 Sep 2014 08:15:39 GMT

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

Re: Problem with Aggregate function

satish_kumar127 — Mon, 22 Sep 2014 09:22:36 GMT

hi peter,

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

regards

satish

Re: Problem with Aggregate function

RaymondGiuseppi — Mon, 22 Sep 2014 09:37:27 GMT

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

Re: Problem with Aggregate function

satish_kumar127 — Mon, 22 Sep 2014 12:07:20 GMT

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