topic Re: Need help with query in Application Development and Automation Discussions

Need help with query

Former Member — Mon, 25 Mar 2013 14:19:29 GMT

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

Re: Need help with query

Former Member — Mon, 25 Mar 2013 15:06:56 GMT

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.

Re: Need help with query

Former Member — Mon, 25 Mar 2013 15:09:18 GMT

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

Re: Need help with query

RaymondGiuseppi — Mon, 25 Mar 2013 15:23:26 GMT

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