topic Re: select sum problem. in Application Development and Automation Discussions

select sum problem.

Former Member — Wed, 12 Nov 2008 08:04:39 GMT

my internal table it_mara conatins matnr and other fields.

I want to select matnr ,omeng from vbbe into another internal table.

i need sum of omeng field based on matnr .

i wrote like this

SELECT matnr sum( omeng ) FROM vbbe INTO TABLE it_vbbe

FOR ALL ENTRIES IN it_mara WHERE matnr = it_mara-matnr .

but iam getting error.

tell me best way of writing the select statement.

thanks in advance.

Regards,

Suresh

Re: select sum problem.

Former Member — Wed, 12 Nov 2008 08:06:24 GMT

wat s the error message

Re: select sum problem.

Former Member — Wed, 12 Nov 2008 08:09:07 GMT

Please specify the eroor.

Re: select sum problem.

Former Member — Wed, 12 Nov 2008 08:10:18 GMT

SELECT matnr sum( omeng )

FROM vbbe

INTO TABLE it_vbbe

FOR ALL ENTRIES IN it_mara

GROUP BY MATNR.

Re: select sum problem.

kesavadas_thekkillath — Wed, 12 Nov 2008 08:37:00 GMT

i dont think we cannot use SUM clause with for all entries.

instead,

SELECT matnr omeng FROM vbbe INTO TABLE it_vbbe

FOR ALL ENTRIES IN it_mara WHERE matnr = it_mara-matnr .

declare a same table like it_vbbe.

then

loop at it_vbbe.

move-corresponding it_vbbe to it.

collect it.

endloop.

now it will have the sum values..

there are some more methos to do this...above one is a ex.

Re: select sum problem.

kesavadas_thekkillath — Wed, 12 Nov 2008 08:37:36 GMT

i dont think we cannot use SUM clause with for all entries.

i think we cannot use SUM clause with for all entries.

Re: select sum problem.

Former Member — Wed, 12 Nov 2008 09:02:49 GMT

iam getting error like

The addition "FOR ALL ENTRIES" excludes all aggregate function with the exception of

"COUNT(*)" as the single element of the SELECT clause .

Regards,

Suresh

Re: select sum problem.

Former Member — Wed, 12 Nov 2008 09:03:41 GMT

change the code as

data : v_sum like vbbe-omeng,

flag.

.................

if not it_mara[] is initial.

SELECT matnr omeng FROM vbbe INTO TABLE it_vbbe

FOR ALL ENTRIES IN it_mara WHERE matnr = it_mara-matnr .

endif.

SORT IT_VBBE BY MATNR.

LOOP AT IT_VBBE.

V_SUM = V_SUM + IT_VBBE-OMENG.

ON CHANGE OF IT_VBBE-MATNR.

FLAG = 'X'.

ENDON.

IF FLAG EQ 'X'.

IT_VBBE-OMENG = V_SUM.

MODIFY IT_VBBE TRANSPORTING OMENG

WHERE MATNR EQ IT_VBBE-MATNR.

CLEAR : V_SUM , FLAG.

ENDIF.

ENDLOOP.

Re: select sum problem.

kesavadas_thekkillath — Wed, 12 Nov 2008 09:12:53 GMT

Hello friend,

thatz wat i have told...we cannot use aggregate functions along with for all entries...

check the other way i specified

Re: select sum problem.

Former Member — Wed, 12 Nov 2008 09:39:45 GMT

don't use ON CHANGE any more, it's obsolete.

sort it_vbbe by matnr.

loop at it_vbbe into wa_vbbe.

at end of matnr.

sum.

wa_vbbe2-matnr = wa_vbbe-matnr.

wa_vbbe2-omeng = wa_vbbe-omeng.

append wa_vbbe2 to it_vbbe2.

endat.

endloop.