Convert Sum Statement to Collect

[Former Member]

Hi,

I am not sure if Collect is better than Select Sum () in terms of performance.

Please advise.

If Collect is better,

please tell me how to convert the following statement to Collect code:

• GET ONHAND QTY FROM TABLE MSPR

----

---

SELECT SUM( PRLAB )

FROM MSPR INTO SUM_ONHAND

WHERE MATNR = EKPO-MATNR

AND WERKS = EKPO-WERKS.

• GET RESERVED QTY (OPEN) FROM TABLE RESB

----

---

SELECT SUM( ENMNG )

FROM RESB INTO SUM_RESERVE

WHERE MATNR = EKPO-MATNR

AND WERKS = EKPO-WERKS

AND XLOEK = SPACE "DELETION INDICATOR

AND KZEAR = SPACE. "FINAL ISSUE INDICATOR

• CALCULATE DIFFERENCE AND FILL V_BACKORDER BASED ON THE CONDITION

----

---

DIFF_ONHAND_RESERVE = SUM_RESERVE - SUM_ONHAND.

IF DIFF_ONHAND_RESERVE > '0'.

V_BACKORDER = 'BACKORDER'.

ENDIF.

[Former Member]

Hi,

I am not sure if Collect is better than Select Sum () in terms of performance.

Please advise.

If Collect is better,

please tell me how to convert the following statement to Collect code:

• GET ONHAND QTY FROM TABLE MSPR

----

---

SELECT SUM( PRLAB )

FROM MSPR INTO SUM_ONHAND

WHERE MATNR = EKPO-MATNR

AND WERKS = EKPO-WERKS.

• GET RESERVED QTY (OPEN) FROM TABLE RESB

----

---

SELECT SUM( ENMNG )

FROM RESB INTO SUM_RESERVE

WHERE MATNR = EKPO-MATNR

AND WERKS = EKPO-WERKS

AND XLOEK = SPACE "DELETION INDICATOR

AND KZEAR = SPACE. "FINAL ISSUE INDICATOR

• CALCULATE DIFFERENCE AND FILL V_BACKORDER BASED ON THE CONDITION

----

---

DIFF_ONHAND_RESERVE = SUM_RESERVE - SUM_ONHAND.

IF DIFF_ONHAND_RESERVE > '0'.

V_BACKORDER = 'BACKORDER'.

ENDIF.

[Former Member]

Hi

Eventhough Select(sum) and collect are used to total up values, their applicability is entirely different. Select(sum) is used to sum a value while reading the database, while collect is used when the data is already available in the ABAP runtime. Collect has the same functionality as APPEND ITAB with the excaption that COLLECT stmt considers all non-numeric fields(type C, N, D, T etc) as key fields. So when a record is 'collected' into an ITAB, it checks if the 'key' already exists and if yes, adds up the numeric fields (TYPE i, p etc) and modifies the existing entry. No new record is created.

Regards

Sharath.

[Former Member]

Hi John,

From the code snippet that you provided it looks like COLLECT will not make any sense as it can be used when appending to an internal table. Here, you are selecting 2 quantities from 2 different tables and trying to set a field based on their difference, there is no appending to any internal table.

To improve performance, instead of COLLECT, you should think in terms of selecting the records from the 2 database tables into 2 internal tables (only once in the beginning) and then looping on one while reading the other for the same key fields. This will avoid using performance intensive SELECT....ENDSELECT loops that you are currently using.

First internal table itab1 can have fields like MATNR WERKS and PRLAB.

Second internal table itab2 can have fields MATNR WERKS and ENMNG.

Then select records from the first database table as:

FIELD-SYMBOLS: <itab1> like line of itab1,
                            <itab2> like line of itab2.

SELECT MATNR WERKS  PRLAB 
FROM MSPR 
INTO TABLE itab1   "SUM_ONHAND
WHERE MATNR = EKPO-MATNR
AND WERKS = EKPO-WERKS.

"select from the second database table
SELECT MATNR WERKS ENMNG 
FROM RESB 
INTO itab2   "SUM_RESERVE
WHERE MATNR = EKPO-MATNR
AND WERKS = EKPO-WERKS
AND XLOEK = SPACE "DELETION INDICATOR
AND KZEAR = SPACE. "FINAL ISSUE INDICATOR


LOOP AT itab1 assigning <itab1>.

READ TABLE itab2 assigning <itab2>
   WITH KEY MATNR = <itab1>-MATNR
                    WERKS = <itab1>-WERKS.
    IF sy-subrc EQ 0.
      DIFF_ONHAND_RESERVE = <itab2>-ENMNG - <itab1>-PRLAB.

    IF DIFF_ONHAND_RESERVE > '0'.

        V_BACKORDER = 'BACKORDER'.

     ENDIF.
   ENDIF.
ENDLOOP.

Hope this helps.

Thanks

Sanjeev

[nivin_varkey]

Hi John,

Given this case..the select sum ( ) statement would definitely be faster than the use of Collect statment. moreover..both sql statements will select the data using the correct indexes

thanks,

Nivin

Edited by: Nivin Joseph Varkey on Jan 14, 2008 7:48 PM