Alternative for double inner join

[Former Member]

SELECT SINGLE ALNUM INTO P_ITAB-ALNUM_AL FROM MAEX

JOIN MARC ON MARC_{MATNR = MAEX}MATNR

JOIN T001W ON T001W_{WERKS = MARC}WERKS

WHERE MAEX~MATNR = P_VBPLP-MATNR

AND MARC~WERKS = T_VBDPL-WERKS

AND MAEX_{ALAND = T001W}LAND1

AND MAEX~GEGRU = 'DE'.

Can anyone suggest the best way to modify the above statement to improve the performence.

Thanks,

Khasimsa

[Former Member]

SELECT SINGLE ALNUM INTO P_ITAB-ALNUM_AL FROM MAEX

JOIN MARC ON MARC_{MATNR = MAEX}MATNR

JOIN T001W ON T001W_{WERKS = MARC}WERKS

WHERE MAEX~MATNR = P_VBPLP-MATNR

AND MARC~WERKS = T_VBDPL-WERKS

AND MAEX_{ALAND = T001W}LAND1

AND MAEX~GEGRU = 'DE'.

Can anyone suggest the best way to modify the above statement to improve the performence.

Thanks,

Khasimsa

[Former Member]

a Join with several tables is no problem by itself, so why do you want to improve it?

The SELECT SINGLE is weird, in a Join you will hardly know whether only one record can fulfill the condition, i.e.

UP TO 1 ROWS would be clearer. But why is only one value needed?

Is the join inside a LOOP?

[Former Member]

yes this join is insde a loop.

[Former Member]

>

> yes this join is insde a loop.

hi

within loop avoid select statement.

use read statement for this issue ,and within loop you are getting one values so check for a rfc or fm for the same.

loop at lt_maex.

read table lt_t001w where matnr = lt_maex-matnr.

if sy-subrc = 0.

endif.

endloop.

[Former Member]

Hi Shaik,

Dont use the select query within the loop.

Performance wise it is not recommended.

Instead you select the above join query in an internal table i.e.,

SELECT a b c, d INTO P_ITAB FROM MAEX

JOIN MARC ON MARC_{MATNR = MAEX}MATNR

JOIN T001W ON T001W_{WERKS = MARC}WERKS

WHERE MAEX~MATNR = P_VBPLP-MATNR

AND MARC~WERKS = T_VBDPL-WERKS

AND MAEX_{ALAND = T001W}LAND1

AND MAEX~GEGRU = 'DE'.

Then

Loop at itab.

read p_itab with key a

endloop.

This will improve your performance as well.

Regards,

Md Ziauddin

[HermannGahm]

Hi,

T001W is a buffered table. The buffer can not be used with the join. Remove the T001W from the join and read it from the buffer separately.

For the remaining tables in the join take a trace.

Check for index support and identical SELECTS.

Kind regards,

Hermann

[Former Member]

many close misses

If the SEKECT is inside a LOOP you should not use a READ in a LOOP, usually the correct coding is a FOR ALL ENTRIES.

The buffered table ... hmm, as long as we do not see the full coding, the buffered table T001W is not the first in the join, maybe it is even to leave it in the join. However, I have some doubts that it is necessary, the coding lloks weird:

AND MAEX_{ALAND = T001W}LAND1

The country LAND1 is not checked on T001W.

Better copy more coding which shows where the values from WHERE condition come from.

Is this working coding (tested correct coding) or still in development ?

Siegfried

[Former Member]

Besides the good point to avoid selects in loops, this may be a little bit better join order:

SELECT SINGLE ALNUM 
  INTO P_ITAB-ALNUM_AL 
  FROM MARC
  JOIN T001W ON T001W~WERKS = MARC~WERKS
  JOIN MAEX ON MAEX~MATNR = MARC~MATNR
           AND MAEX~ALAND = T001W~LAND1
 WHERE MARC~MATNR = P_VBPLP-MATNR
   AND MARC~WERKS = T_VBDPL-WERKS
   AND MAEX~GEGRU = 'DE'.

[ravi_lanjewar]

Hi,

Don't used table t001w in join, While using buffer table in join operation it will not read data from buffer,

1) Read data from table t001w

2) apply join on table marc and maex and also put where condition with werks because marc table having primary key with fields matnr and werks so put both in where condition.

   select single * from t001w client specified
     where mandt = sy-mandt
          and werks =  T_VBDPL-WERKS.

  SELECT SINGLE ALNUM  INTO P_ITAB-ALNUM_AL 
     FROM MARC  JOIN MAEX ON MAEX~MATNR = MARC~MATNR
     WHERE MARC~MATNR = P_VBPLP-MATNR
         AND MARC~WERKS = T_VBDPL-WERKS
         AND MAEX~ALAND =  T001W~LAND1
         AND MAEX~GEGRU = 'DE'.

[Former Member]

This message was moderated.