Join for MARC & MARD

[Former Member]

Hi Friends,

I need to tune the below SELECT statement. Please let me know if join will help. If so, please give the code for the below query.

SELECT matnr werks dismm mmsta sobsl FROM marc INTO TABLE i_marc

FOR ALL ENTRIES IN i_mard

WHERE matnr EQ i_mard-matnr

AND werks EQ i_mard-werks

AND dismm IN s_dismm

AND mmsta IN s_mmsta

AND sobsl IN s_sobsl

AND lvorm NE 'X'.

Thanks,

Dikshitha

[Former Member]

Hi Friends,

I need to tune the below SELECT statement. Please let me know if join will help. If so, please give the code for the below query.

SELECT matnr werks dismm mmsta sobsl FROM marc INTO TABLE i_marc

FOR ALL ENTRIES IN i_mard

WHERE matnr EQ i_mard-matnr

AND werks EQ i_mard-werks

AND dismm IN s_dismm

AND mmsta IN s_mmsta

AND sobsl IN s_sobsl

AND lvorm NE 'X'.

Thanks,

Dikshitha

[kesavadas_thekkillath]

your query is fine jus change lvorm NE 'X' as lvorm = space.

SELECT matnr werks dismm mmsta sobsl FROM marc INTO TABLE i_marc
FOR ALL ENTRIES IN i_mard
WHERE matnr EQ i_mard-matnr
AND werks EQ i_mard-werks
and lvorm = space
AND dismm IN s_dismm
AND mmsta IN s_mmsta
AND sobsl IN s_sobsl.

[Rui_Dantas]

Hello,

How are you filling I_MARD?

You should use the same query, linking MARD and MARC by MATNR + WERKS, and not use two queries with for all entries.

(ps: you if do want to use for all entries, check if the performance problem occurs when I_MARD is empty, because with for all entries EMPTY means ALL, and not NONE as you might expect).

[Former Member]

DATA: i_mard_tmp LIKE table i_mard.

* Check to see if table i_mard is initial. If it is, you should not
* use it in the select statement with for all entries
IF NOT i_mard[] IS INITIAL.

  i_mard_tmp[] = i_mard[].
  
  SORT i_mard_tmp BY matnr werks.
  
* MARD stores information based on material plant and location. You could have
* multiple records in table i_mard for each material and plant combination.
* Having unique records for the material and plant combination should reduce
* the load on your select statement. Now you will not have redundant entries
  DELETE ADJACENT DUPLICATES FROM i_mard_tmp COMPARING matnr werks.

  SELECT matnr
         werks
         dismm
         mmsta
         sobsl
  FROM marc
  INTO TABLE i_marc
  FOR ALL ENTRIES IN i_mard_tmp
  WHERE matnr EQ i_mard_tmp-matnr
  AND   werks EQ i_mard_tmp-werks
  AND   dismm IN s_dismm
  AND   mmsta IN s_mmsta
  AND   sobsl IN s_sobsl
  AND   lvorm NE 'X'.

ENDIF.

Use transdaction ST05 to trace this select statement during execution and verify if it is using the primary index in table MARC. This code should be good enough. Let me know if this helps.

[Former Member]

Hello Dikshita,

Query seems to be fine. Make sure if there are entrie in i_mard during the query on MARC, where you are doing a "FOR ALL ENTRIES" on i_mard.

If i_mard has no values, it means you would be selecting all the entries from MARC which will affect the run time. Use "IF NOT i_mard IS INITIAL" before the query in MARC.

[venkatasriram_mygapula]

hi,

your query is fine for performance wise. before selecting fields from second table for all entries in first table should be mentions like

if i_mard[] is not initial.

[Former Member]

Code is fine.

Try to improve performance by doing ABAP code analysis.

[DipeshKothari]

Based upon requirement - try below views:

V_MMIM_EN View of Sales Order Stocks

V_MMIM_KN View of Consignment Stocks

V_MMIM_LB View of Warehouse Stocks of Split-Valuated Material

V_MMIM_LC View of Batch Material Stocks On Hand

V_MMIM_LN View of Non-Batch Material Stocks On Hand

V_MMIM_QN View on Project Stocks