Need help with query

[Former Member]

Hello,

I'm not a ABAP programmer and have to make this change and need your assistance. Thanks.

CLEAR: IPDCOM, IPDCOM[].

SELECT * FROM  CC1TEV

UP TO L_SELECT_LIMIT ROWS

WHERE PDSNR GT L_LAST_PDSNR

ORDER BY PRIMARY KEY.

MOVE-CORRESPONDING CC1TEV TO IPDCOM.

MOVE CC1TEV-TRFGR TO IPDCOM-LOGRR.

APPEND IPDCOM.

ENDSELECT.

I need to change the code, so it checks if IPDCOM-PDSNR exist in a table called TEVEN. If it exist I have to remove them and save in another table.

I added this code:

   LOOP AT IPDCOM.

      SELECT SINGLE * FROM TEVEN WHERE TEVEN~PDSNR EQ IPDCOM-PDSNR.        *This affects the performance

      IF SY-SUBRC = 0.

        MOVE-CORRESPONDING IPDCOM TO ITEVEN.

        APPEND ITEVEN.

        DELETE IPDCOM.

        CLEAR ITEVEN.

      ENDIF.

    ENDLOOP.

    INSERT ZTEVDC FROM TABLE ITEVEN.

  Any suggestions to improve the performance?

Thanks

Turan

[Former Member]

I want to make sure that no values from Internal table IPDCOM(Field pdsnr) exist in the table TEVEN. These duplicates only occurs a couple of times in a year as an error and we need to remove them, and save them in a separate table.

[Former Member]

Egemen,

As you had, having a select inside a loop is not good in terms of performance. I would suggest doing this:

IF ipdcom[] IS NOT INITIAL.

SELECT * FROM TEVEN INTO lt_teven

FOR ALL ENTRIES IN ipdcom

WHERE PDSNR IN IPDCOM-PDSNR.

ENDIF.

LOOP AT ipdcom INTO wa_ipdcom.

  CLEAR wa_teven.

   READ TABLE lt_teven INTO wa_teven WITH KEY pdsnr = wa_ipdcom-pdsnr.

      IF SY-SUBRC IS INITIAL.

           MOVE  data into another table.

           DELETE from ipdcom on the current row (sy-tabix)

       ENDIF.

ENDLOOP.

Hope this helps.

thanks,

Vikram.M

[RaymondGiuseppi]

Why do you want to check duplicate in Infotype 2011 (TEVEN) are there not standard Customizing options in HR time that do this already ?

If the batch of time events is too big and the FOR ALL ENTRIES give yet performance issues, even if better that SELECT SINGLE in a LOOP, you could try to select every records in TEVEN where pdsnr GT l_last_pdsnr (same selection as interface file CC1TEV)

Another solution is to check at first select the non-existence of record in TEVEN

SELECT * FROM  cc1tev UP TO l_select_limit ROWS
  WHERE pdsnr GT l_last_pdsnr 
    AND NOT EXISTS ( SELECT * FROM TEVEN WHERE pdsnr EQ cc1tev~pdsnr )
  ORDER BY PRIMARY KEY.

Anyway, you can also change your SELECT/ENDSELECT with an SELECT INTO TABLE, then LOOP to fill final table.

Also, did you check for an index on TEVEN, which can be a big table...

Regards,

Raymond