topic Re: Splitting the Join condition in Application Development and Automation Discussions

Splitting the Join condition

Former Member — Fri, 15 May 2020 11:06:27 GMT

Hi experts,

I hope you are doing well. I have a requirement to break the below select query without using JOIN, could anyone help me to do this.

SELECT a~mblnr
a~mjahr
a~bldat
a~budat
a~xblnr
b~matnr
b~werks
b~lgort
b~erfmg
b~erfme
b~j_3asiz
b~j_4ksca FROM mkpf AS a JOIN mseg AS b ON ( a~mblnr EQ b~mblnrAND a~mjahr EQ b~mjahr ) INTO TABLE g_it_mkpf_mseg

FOR ALL ENTRIES IN g_it_sales

WHERE a~xblnr EQ g_it_sales-vbeln_dlv AND b~shkzg EQ 'S'.

Re: Splitting the Join condition

pfefferf — Fri, 15 May 2020 11:09:28 GMT

Maybe you want to describe why you want to do that? If you need data of both tables than a join is necessary, except you do the ugly way with two selects and combining the data on the application server level.

Re: Splitting the Join condition

MateuszAdamus — Fri, 15 May 2020 11:14:34 GMT

Hi aya743

You could do it like this:

IF g_it_sales[] IS NOT INITIAL.
  SELECT mblnr, mjahr, bldat, budat, xblnr
    FROM mkpf 
    FOR ALL ENTRIES IN @g_it_sales
    WHERE xblnr = @g_it_sales-vbeln_dlv
    INTO TABLE @DATA(lt_mkpf).
  IF sy-subrc = 0.
    SELECT mblnr, mjahr, zeile, matnr, werks, lgort, erfmg, erfme, j_3asiz, j_4ksca 
      FROM mseg 
      FOR ALL ENTRIES IN @lt_mkpf
      WHERE mblnr = @lt_mkpf-mblnr
        AND mjahr = @lt_mkpf-mjahr
        AND shkzg = 'S'
      INTO TABLE @DATA(lt_mseg).
  ENDIF.
ENDIF.

regards,

Mateusz

Re: Splitting the Join condition

former_member1716 — Fri, 15 May 2020 11:21:17 GMT

Hello aya743,

Please be noted that JOIN should be preferred over multiple select queries to avoid multiple database fetch.

Your select query above has few flaws, however to answer your question in splitting the select query without join you have to understand the below points.

1) Your FOR ALL ENTRIES table is only applicable for MKPF, so your first select query must be on MKPF with the FOR ALL ENTRIES of the table G_IT_SALES.

2) To connect the first query on MKPF to the table MSEG you must have common key fields which is MBLNR and MJAHR in this case. Hence your second query would be on MSEG with FOR ALL ENTRIES on internal table fetched from MKPF along with the where condition on SHLZG.

Below code is just pseudo code for your understanding.

  SELECT mblnr
         mjahr
         bldat
         budat
         xblnr
 FROM mkpf
 INTO TABLE g_it_mkpf
 FOR ALL ENTRIES IN g_it_sales
 WHERE a~xblnr EQ g_it_sales-vbeln_dlv.
  IF sy-subrc EQ 0.
    SORT g_it_mkpf.
    SELECT mblnr
           mjahr
           matnr
           werks
           lgort
           erfmg
           erfme
           j_3asiz
           j_4ksca
  FROM mseg
  INTO TABLE g_it_mseg
  FOR ALL ENTRIES IN g_it_mkpf
  WHERE mblnr EQ g_it_mkpf-mblnr AND
        mjahr EQ g_it_mkpf-mjahr AND
        shkzg EQ 'S'.
    IF sy-subrc EQ 0.
      SORT g_it_mseg.
    ENDIF.
  ENDIF.

Regards!

Re: Splitting the Join condition

Sandra_Rossi — Fri, 15 May 2020 17:06:33 GMT

I would keep the join and replace FOR ALL ENTRIES with GTT or other solution 😛

Re: Splitting the Join condition

Former Member — Sat, 16 May 2020 07:57:34 GMT

Hi,

I think you are trying to optimize the query as it might be taking time.You can proceed as satishkumarbalasubramanian or mateuszadamus reply adding a delete adjacent duplicates before the first fetch on mkpf on basis of vbeln_dlv.
You can also try to find a way to use primary key so that time is reduced.

Regards
Abhishek

Re: Splitting the Join condition

Former Member — Wed, 20 May 2020 04:17:30 GMT

Thanks for your help Adamus 🙂

Re: Splitting the Join condition

Former Member — Wed, 20 May 2020 04:18:02 GMT

Thanks for your help Satish 🙂