topic Re: Select with aggregate expression SUM in Application Development and Automation Discussions

Select with aggregate expression SUM

Former Member — Mon, 15 Dec 2008 16:40:13 GMT

Hi experts,

I have a select with the expression SUM shown below:

SELECT a_{spmon a}werks a_{mdv01 a}matnr b~prodh

SUM( a~m_pagados ) AS m_pagados

into table lt_zmmfb

FROM zmmfb01 AS a INNER JOIN mvke AS b ON a_{matnr = b}matnr

INNER JOIN mara AS c ON a_{matnr = c}matnr

CLIENT SPECIFIED

WHERE a~mandt = '010'

AND a~bukrs = 'COBE'

AND a~spmon >= '200601'

AND a~mdv01 NOT IN ('LMA', 'LTR')

AND c~mtart = 'ZPAC'

GROUP BY a_{spmon a}werks a_{mdv01 a}matnr b~prodh

ORDER BY a_{spmon a}werks.

I am currently testing the code with only 3 entries in the custom db table which are below:

MANDT SPMON BUKRS WERKS MDV01 MATNR M_PAGADOS

010 200601 COBE 0101 L05 000000000000000032 1,000.00000

010 200603 COBE 0104 L05 000000000000002173 2.25000

010 200603 COBE 0104 L05 000000000000002193 3.00000

Now comes the problem. When I execute the program my resulting internal table looks like this:

SPMON WERKS MDV01 MATNR PRODH M_PAGADOS

200601 0101 L05 000000000000000032 __________________ 2000.000

200601 0101 L05 000000000000000032 000001001001030032 14000.000

200603 0104 L05 000000000000002173 000001001001030031 15.750

200603 0104 L05 000000000000002193 000001001001030031 21.000

None of the materiales have the Hierarchy code (PRODH) assigned in the table MVKE but for some reason the values above appear (except for the first entry) . Also if you haven't noticed there are 4 entries ( should only be three) in the table. What's more it has doubled the first value of M_PAGADOS, the second value is 7x the 1st and the others are both the origional value x7.

Any clues anyone? It's been doing my head in for a while and I have no idea why it's doing this.

Regards,

Simon Kerr

Re: Select with aggregate expression SUM

Former Member — Tue, 16 Dec 2008 13:12:18 GMT

These 3 entries are in your custom table, but what happens when you remove all the sum and group by from your SQL and run it with just the joins? How many rows are returned then.

If joining to another table causes more than three rows to be returned, you'll multiply your aggregated values.

Re: Select with aggregate expression SUM

rainer_hbenthal — Tue, 16 Dec 2008 13:47:22 GMT

Resulting set for inner join

The inner join joins the columns of every selected line on the left- hand side with the columns of all lines on the right-hand side that jointly fulfil the join_cond condition. A line in the resulting set is created for every such line on the right-hand side. The content of the column on the left-hand side may be duplicated in this case. If none of the lines on the right-hand side fulfils the join_cond condition, no line is created in the resulting set.

Re: Select with aggregate expression SUM

Former Member — Tue, 16 Dec 2008 14:06:50 GMT

Hi Simon,

The unique key of table mvke contains two more fields except matnr. Probably there are more then one entry for material 000000000000000032.

You need to include the other fields ( VKORG, VTWEG) in your select statement as well.

Regards

Kameliya

Re: Select with aggregate expression SUM

rainer_hbenthal — Tue, 16 Dec 2008 14:10:17 GMT


>Probably there are more then one entry for material 000000000000000032.
> Kameliya

There are two of them

Re: Select with aggregate expression SUM

Former Member — Tue, 16 Dec 2008 15:34:44 GMT

Thanks everyone,

I solved the problem by taking out the CLIENT SPECIFIED statement. I understood that to do a select using the mandate field you had to use the client specified statement (true for some dbtables) but is seems it is not required here. I'm not really sure what it was doing but it's solved now.

Thanks again,

Simon