Select with join condition or not?

[Former Member]

Dear all,

Do you think it would be more efficient to build a select with a join condition for the following query?

• Material number where unrestricted quantity <> 0 are retrieved

SELECT matnr labst INTO CORRESPONDING FIELDS OF TABLE tbl_mard_entry

FROM mard WHERE werks = p_plant AND lgort IN so_store AND labst <> 0

AND matnr IN so_matnr.

• Filtering material type and group

SELECT matnr INTO CORRESPONDING FIELDS OF TABLE tbl_mara_matnr FROM mara

FOR ALL ENTRIES IN tbl_mard_entry WHERE matnr = tbl_mard_entry-matnr

AND mtart IN so_mtype AND matkl IN so_mgrp.

Regards.

[Former Member]

I think a join would be preferable, eg

SELECT matnr labst INTO CORRESPONDING FIELDS OF TABLE tbl_mard_entry

FROM mara

join mard on mard_{matnr = mara}matnr

WHERE mara~matnr in so_matnr

and mard~werks = p_plant

AND mard~lgort IN so_store

AND mard~labst <> 0

.

But even if you didn't use a join I think you should reverse the order of the selects and target MARA first as the hitlist will be smaller and then target MARD.

eg,

• Get applicable materials

SELECT matnr INTO CORRESPONDING FIELDS OF TABLE tbl_mara_matnr FROM mara

WHERE matnr IN so_matnr

AND mtart IN so_mtype

AND matkl IN so_mgrp.

• get MARDs

SELECT matnr labst INTO CORRESPONDING FIELDS OF TABLE tbl_mard_entry

FROM mard

FOR ALL ENTRIES IN tbl_mara_matnr

WHERE matnr = tbl_mara_matnr-matnr

and werks = p_plant

AND lgort IN so_store

AND labst <> 0

.

[Former Member]

Hi Nozome,

U can write select ststement to join MARA & MARD. It would be more efficient.

Select A_{matnr B}matnr B~labst into corresponding field of table

itab from MARA as A

inner join MARD as B

on A_{matnr = B}matnr

and werks = p_plant and labst <> 0

where matnr in s_matnr and

mtart in so_mtype and matkl in so_mgrp

and lgort in so_store.

Reward points if possible.

Regards,

Hemant

[Former Member]

using 'For all entry' is always efficient than using join. You can check the execution time of the both form in SE30 transaction. You will see there, that using 'For all entry' take much lesser time than using join.

[former_member186741]

I think a join would be preferable, eg

SELECT matnr labst INTO CORRESPONDING FIELDS OF TABLE tbl_mard_entry

FROM mara

join mard on mard_{matnr = mara}matnr

WHERE mara~matnr in so_matnr

and mard~werks = p_plant

AND mard~lgort IN so_store

AND mard~labst <> 0

.

But even if you didn't use a join I think you should reverse the order of the selects and target MARA first as the hitlist will be smaller and then target MARD.

eg,

• Get applicable materials

SELECT matnr INTO CORRESPONDING FIELDS OF TABLE tbl_mara_matnr FROM mara

WHERE matnr IN so_matnr

AND mtart IN so_mtype

AND matkl IN so_mgrp.

• get MARDs

SELECT matnr labst INTO CORRESPONDING FIELDS OF TABLE tbl_mard_entry

FROM mard

FOR ALL ENTRIES IN tbl_mara_matnr

WHERE matnr = tbl_mara_matnr-matnr

and werks = p_plant

AND lgort IN so_store

AND labst <> 0

.

[Former Member]

Hi Nozome,

You can write a join on the two db tables i.e. Mara and Mard. But then 'for all entries' instead of join condition is suggested more as it takes lesser time for execution. So, I suggest you to go in the way you are doing now.

Regards,

Jayant

[Former Member]

Hi Cany,

For all entries is preferable instead of join option.

[Former Member]

hi Cany,

When we use joins some dis adv are there since the readability will not be there...If u feel ok we can use it by for all entries concept.