Issue with SQL Query Result Differ From Manual Table Execution - EKPO & EKBE

[UdeshiWithanage]

Hello SAP Community,

I’m facing an issue with a SQL query in an ABAP report that uses a JOIN and a SUM aggregation.

Example Scenario:

For a specific matnr (material number) and werks (plant), along with the other conditions, the expected SUM of quantity is 30. But the query during execution returns 120.

Problem:

When I manually pass the inputs in table level I get the correct result. However, when the same inputs are passed in query and is executed during runtime with the values passed through variables, it returns incorrect results.

Strangely, this issue occurs only in the PRD system – in DEV, it works as expected.

Here is the query I’m using:

SELECT SUM( CASE  WHEN _ekbe~shkzg = 'H'
                          THEN _ekbe~menge * ( -1 )
                          ELSE  _ekbe~menge
                   END )
       FROM ekpo AS _ekpo
       INNER JOIN ekbe AS _ekbe
         ON _ekbe~matnr EQ _ekpo~matnr
         AND _ekbe~ebeln EQ _ekpo~ebeln
         AND _ekbe~bwart IS NOT INITIAL
       WHERE _ekpo~matnr = @meprnitem-matnr
       AND _ekpo~werks = @meprnitem-werks
       AND _ekpo~elikz IS INITIAL
       AND _ekpo~loekz IS INITIAL
       INTO @LV_quant_ekbe.

[Sandra_Rossi]

With a join, the lines are duplicated based on your join condition. You can check manually by yourself, in production, which are the lines in EKPO and EKBE corresponding to your selection (MATNR and WERKS), and you'll understand what happens. After you have understood the reason, you can explain to the client and ask what to do.