CODEWITHSUNDEEP
inner-joinabapopensql
SmartieHead
SmartieHead

Reputation: 63

Joining various tables using FOR ALL ENTRIES

I have created a method for fetching data from tables. My importing parameters are IV_RCP_NO, IV_VERS, IV_ALT and exporting parameter is ET_TABLE.

Now I am using INNER JOIN for joining 4 tables but the requirement says it should be done using FOR ALL ENTRIES clause.

Can someone please help me out to proceed with it?

Here is my code:

  SELECT RCP_TBL~SEARCH_TERM PLNT_TBL~PLANT DESCR_TBL~DESCR RCP_STATUS~DESCR 
     INTO TABLE ET_TABLE
     FROM ESTRH AS ESTRH_TBL
     INNER JOIN /PLMB/RCP_RECIPE AS RCP_TBL
       ON ESTRH_TBL~RECN = RCP_TBL~SUBRECN
     INNER JOIN /PLMB/SAM_NODE_T AS RCP_STATUS
       ON RCP_STATUS~STATUS_ID = RCP_TBL~STATUS
     INNER JOIN /PLMB/RCP_PLNT AS PLNT_TBL
       ON RCP_TBL~RCP_GUID = PLNT_TBL~RCP_GUID
     INNER JOIN /PLMB/RCP_DESCR AS DESCR_TBL
       ON PLNT_TBL~RCP_GUID = DESCR_TBL~OBJECT_GUID 
       AND RCP_TBL~RCP_GUID = DESCR_TBL~OBJECT_GUID
     WHERE SUBID = IV_RCP_NO AND ALT_NO = IV_ALT AND VERS_NO = IV_VERS.

Upvotes: 0

Views: 10163

Answers (2)

Florian
Florian

Reputation: 5101

If the join can be done without for all entries then do that. This will yield best performance.

For all entries is a tool to simplify queries where you already have part of the data in an internal table, from some previously executed code or pre-computation. It effectively results in a series of independent selects whose results are merged after individual completion - meaning this results in multiple database roundtrips, which can drastically worsen performance.

If you have specific requirements to apply the for all entries, you should clarify which part of the data is already there and needs to be joined that way. Otherwise any suggestion from StackOverflowers will remain inefficient guesswork.

Upvotes: 1

divScorp
divScorp

Reputation: 502

You can refer the below code-

* Local structure
TYPES: BEGIN OF ty_estrth,
        subid TYPE  esesubid,
        recn  TYPE  eserecn,
      END OF ty_estrth.

* Internal table
DATA: lt_estrh TYPE STANDARD TABLE OF ty_estrth.

SELECT subid recn FROM estrh
  INTO TABLE lt_estrh  
  WHERE subid = iv_rcp_no.

IF lt_estrh IS NOT INITIAL.
  SELECT * FROM /plmb/rcp_recipe
    INTO TABLE lt_rcp_recipe    "Internal table of type /PLMB/RCP_RECIPE
    FOR ALL ENTRIES IN lt_estrh
    WHERE subrecn = lt_estrh-recn
      AND alt_no = iv_alt AND vers_no = iv_vers.

  IF lt_rcp_recipe IS NOT INITIAL.
    SELECT * FROM /plmb/sam_node_t
      INTO TABLE lt_sam_node_t     "Internal table of type /PLMB/SAM_NODE_T
      FOR ALL ENTRIES IN lt_rcp_recipe
      WHERE status_id = lt_rcp_recipe-status.

    SELECT * FROM /plmb/rcp_plnt
      INTO TABLE lt_rcp_plnt   " Internal table of type /PLMB/RCP_PLNT
      FOR ALL ENTRIES IN lt_rcp_recipe
      WHERE rcp_guid = lt_rcp_recipe-rcp_guid.

    SELECT * FROM /plmb/rcp_descr
      INTO TABLE lt_rcp_descr    " Internal table of type /PLMB/RCP_DESCR
      FOR ALL ENTRIES IN lt_rcp_recipe
      WHERE rcp_guid = lt_rcp_recipe-rcp_guid.
  ENDIF.
ENDIF.

You'll get your data in below internal tables

lt_rcp_recipe
lt_sam_node_t
lt_rcp_plnt
lt_rcp_descr

Better declare a local structure with specific fields which you want to read, as i declared above. After that you have to read and fill data in exporting table ET_TABLE.

Upvotes: 0

Related Questions