link STXH and CRMD_ORDER_INDEX

[former_member206621]

I have the following code:

DATA: BEGIN OF it_act_search OCCURS 0.
DATA: guid      TYPE crmt_object_guid.
DATA: post_date TYPE crmt_posting_date.
DATA: END OF it_act_search.

SELECT DISTINCT header posting_date_hd
  FROM crmd_order_index
  INNER JOIN but000 ON crmd_order_index~partner_no = but000~partner
  APPENDING table it_act_search
  WHERE ...

SELECT *
  FROM stxh
  INTO CORRESPONDING FIELDS OF TABLE it_stxh
  WHERE tdspras  = lv_spras
  AND   tdid     IN note_types_sel
  AND   tdobject = 'CRM_ORDERH'.

LOOP AT it_act_search INTO wa_act_search.
  READ TABLE it_stxh WITH KEY tdname = wa_act_search-guid.
  IF sy-subrc <> 0.
    DELETE TABLE it_act_search FROM wa_act_search.
  ENDIF.
ENDLOOP.

I now want to eliminate the LOOP because this is taking too much time. How can I link STXH and CRMD_ORDER_INDEX?

I already tried:

SELECT *
  FROM stxh
  INTO CORRESPONDING FIELDS OF T
  FOR ALL ENTRIES IN it_act_sear
  WHERE tdspras = lv_spras
  AND tdname = it_act_search_temp-guid
  AND tdid IN note_types_sel
  AND tdobject = 'CRM_ORDERH'.

but then I get the error message: "When you use the addition "FOR ALL ENTRIES IN itab", the fields "TDNAME" and "IT_ACT_SEARCH_TEMP-GUID" must have the same type and the same length"

Kind regards,

Lieselot

[Former Member]

Hi Lieselot,

Using a loop is a good thing, but you have to code it carefully : ABAP is faster than queries on database!

As mentioned in the previous posts, you should use BINARY SEARCH for your READ : it will be much faster. But you can let the DELETE in the loop but deleting regarding the current recording. Your code could be like this one :

" In order to use BINARY SEARCH, you first have to sort the table
SORT it_stxh BY tdname.

" Loop over the records
LOOP AT it_act_search INTO wa_act_search.
  READ TABLE it_stxh WITH KEY tdname = wa_act_search-guid
    BINARY SEARCH.
  IF sy-subrc  0.
" Delete the current record
    DELETE it_act_search.
  ENDIF.
ENDLOOP.

Best regards,

Samuel

[former_member206621]

solved the issue by using the following code:

    LOOP AT it_act_search ASSIGNING <del_act>.
      READ TABLE it_stxh WITH KEY tdname = <del_act>-guid BINARY SEARCH.
      IF sy-subrc <> 0.
        <del_act>-delete = 'X'.
      ENDIF.
    ENDLOOP.

    DELETE it_act_search WHERE delete = 'X'.

thanks for your help!

Kind regards,

Lieselot

[Pawan_Kesari]

Move that DELETE out of LOOP. Consider using BINARY search in READ statement

DATA: BEGIN OF it_act_search OCCURS 0.
DATA: guid      TYPE crmt_object_guid.
DATA: post_date TYPE crmt_posting_date.
DATA: flag      TYPE char01 .
DATA: END OF it_act_search.

FIELD-SYMBOLS <fs_act> LIKE LINE OF it_act_search .


SELECT DISTINCT header posting_date_hd
  FROM crmd_order_index
  INNER JOIN but000 ON crmd_order_index~partner_no = but000~partner
  APPENDING table it_act_search
  WHERE ...

SELECT *
  FROM stxh
  INTO CORRESPONDING FIELDS OF TABLE it_stxh
  WHERE tdspras  = lv_spras
  AND   tdid     IN note_types_sel
  AND   tdobject = 'CRM_ORDERH'.

LOOP AT it_act_search ASSIGNING <fs_act> .
  READ TABLE it_stxh WITH KEY tdname = wa_act_search-guid.
  IF sy-subrc  = 0.
    <fs_act>-flag = 'X' .
  ENDIF.
ENDLOOP.

DELETE it_act_search WHERE flag = 'X' .

[Sm1tje]

There might be two options to solving this issue:

1. You should be able to optimize the LOOP statement by using TYPE SORTED TABLE OF stxh with UNIQUE KEY.

2. Create an intermediate internal table in which you store all selected GUID's in an internal table with type of TDNAME. In this case you have the same field types for your FOR ALL ENTRIES statement. You can also add an additional column with the GUID's and type of TDNAME (would be more practical since you are still working with the same itab).

I would prefer the second option because in that case you are not selecting any redundant records which later will be deleted anyway (according to your code extract).