topic Re: select SUM( ) in inner join - error in code in Application Development and Automation Discussions

select SUM( ) in inner join - error in code

Former Member — Tue, 23 Oct 2007 14:16:49 GMT

Hi All,

The following code is using ABAP OO and I get the following error

E:The addition "FOR ALL ENTRIES" excludes all aggregate functions with

the exception of "COUNT( * )", as the single element of the SELECT

clause.

Here is the Code:

select a_{/bic/ZEDM_ENTP a}material a~/bic/zedm_stid

a_{/bic/ZEDM_SDT a}/bic/ZEDM_MINH a~/bic/ZEDM_EHD

SUM( b~/bic/ZEDM_QPLN )

c~/bic/ZDEMQTY

from /bic/azdrp_1900 as a inner join /bic/azdrp_0400 as b

a_{/bic/ZEDM_ENTP = b}/bic/ZEDM_ENTP and

a_{material = b}material and

a_{/bic/zedm_stid = b}plant and

a_{/bic/ZEDM_SDT = b}/bic/zedm_pshp

inner join /bic/azrp_0800 as C

a_{material = c}material and

a_{/bic/zedm_stid = c}/bic/zedm_stid and

a_{/bic/ZEDM_SDT = c}/bic/ZEDM_ESDT

into table z_it_inv for all entries in

RESULT_PACKAGE where

a~/bic/ZEDM_ENTP = RESULT_PACKAGE-/bic/ZEDM_ENTP

and a~material = RESULT_PACKAGE-material

and a~/bic/ZEDM_SDT = RESULT_PACKAGE-/BIC/OIZEDM_EDT

and a~/bic/zedm_stid = RESULT_PACKAGE-/BIC/OIZEDM_TOST

group by b_{/bic/ZEDM_ENTP b}material b~plant

b~/bic/zedm_pshp

Can you please tell me how to correct this?

Thanks

Re: select SUM( ) in inner join - error in code

Former Member — Tue, 23 Oct 2007 14:25:09 GMT

When you are using for 'all entries' you can not use SUM because it is an aggregate function.

Re: select SUM( ) in inner join - error in code

Former Member — Tue, 23 Oct 2007 14:50:12 GMT

Krishna,

Is there a workaround for this? I need to use for all entries and want to do the summation of a particular colum.

Thanks

Deepa

Re: select SUM( ) in inner join - error in code

Former Member — Tue, 23 Oct 2007 14:52:36 GMT

Aggregate functions are not very efficient. Try doing the SUM after the data is selected.

Rob

Re: select SUM( ) in inner join - error in code

Former Member — Tue, 23 Oct 2007 14:53:35 GMT

Hi,

You can get all records first and then LOOP AT Internal table and use COLLECT statement to sum up required column.

This will be a better idea than trying to use SUM in SELECT with JOINS.

ashish