topic Re: Doubts in using INNER JOIN in Application Development and Automation Discussions

Doubts in using INNER JOIN

Former Member — Mon, 10 Dec 2007 11:13:06 GMT

Hi Experts,

I am working on customizing a stock summary report.

I have to output the details of Materials which are deleted in a given time period.

I have 10 select-options from 4 different tables i.e MARA, MARC, MBEW & EKPO.

I am using innner join for the same.

My doubts are:

1. Is it the best way?????

2. How do i find out the Quantity of material which was deleted???

3. Are there any tables where i can search for BUKRS(Compnay code) & WERKS(Plant) based on MATNR (Material Number)????

Re: Doubts in using INNER JOIN

former_member386202 — Mon, 10 Dec 2007 11:16:05 GMT

Hi,

Dont use joins it will affect on performance better to use FOR ALL ENTRIES

Refer this code

&----

*& Form sub_read_mara *

&----

This form will Select the material number material type material *
group & batch management indicator from table mara using given *
material number *

----

FORM sub_read_mara .

*--Select query to pick the material number material group material type

and batch management from table MARA

SELECT matnr "Material Number

mtart "Material Type

matkl "Material Group

xchpf "Batch Management

FROM mara "Material Master

INTO TABLE i_mara

WHERE matnr IN s_matnr

AND mtart IN s_mtart

AND matkl IN s_matkl .

*--Check Subrc

IF sy-subrc <> 0.

*--No data found for the given selection criteria.

MESSAGE i001.

LEAVE TO LIST-PROCESSING.

ELSE.

*--Sort table by Material Number

SORT i_mara BY matnr.

ENDIF.

ENDFORM. "sub_read_mara

&----

*& Form sub_read_marc *

&----

This form will Select the material number plant purchasing group & *
MRP controller from table MARC using given material number plant & *
purchasing group. *

----

FORM sub_read_marc .

IF NOT i_mara[] IS INITIAL.

*--Select query to pick the material number plant purchasing group &

MRP controller from table MARC

SELECT matnr "Material Number

werks "Plant

ekgrp "Purchasing Group

dispo "MRP Controller

FROM marc "Material Master

INTO TABLE i_marc

FOR ALL ENTRIES IN i_mara

WHERE matnr = i_mara-matnr

AND werks IN s_werks

AND ekgrp IN s_ekgrp

AND dispo IN s_dispo.

*--Check Subrc

IF sy-subrc <> 0.

MESSAGE i001.

LEAVE TO LIST-PROCESSING.

ELSE.

*--Sort table by Material Number Plant

SORT i_marc BY matnr werks.

ENDIF.

ENDFORM. "sub_read_marc

&----

*& Form sub_read_t320 *

&----

This form retrieve warehouse number from table t320 *

----

FORM sub_read_t320 .

IF NOT i_marc[] IS INITIAL.

*--Select query to pick the plant storage location & warehouse number

SELECT werks "Plant

lgort "Storage Location

lgnum "Warehouse Number

FROM t320

INTO TABLE i_t320

FOR ALL ENTRIES IN i_marc

WHERE werks = i_marc-werks

AND lgort IN s_lgort.

*--Check Subrc

IF sy-subrc = 0.

*--Sort table by Plant Storage Location

SORT i_t320 BY werks lgort.

ENDIF.

ENDFORM. "sub_read_t320

Regards,

Prashant

Re: Doubts in using INNER JOIN

Former Member — Mon, 10 Dec 2007 11:16:33 GMT

hi,

u can fine werks from MARC

Re: Doubts in using INNER JOIN

Former Member — Mon, 10 Dec 2007 11:24:41 GMT

Thanks Prashant.

this solves first part of my question.

but i want to know the quantity & value of material which was deleted.

how do i go about it???

Thanks.

Re: Doubts in using INNER JOIN

Former Member — Mon, 10 Dec 2007 11:29:12 GMT

Hi Gaurav,

as we alll know tht using inner join is a performance issue i suggest not to use this instead use for all entries this might avoid the performance issue.

if u want to check the tables u need go to tcode se15 or se84 and give the field names and u can get the tables .

i am sending the links between the tables since u can get the exact relationships between thm....

http://www.erpgenie.com/abap/tables.htm

Hope this might be helpful to u

Regards,

Sana M.

reward if helpful.....

Re: Doubts in using INNER JOIN

Former Member — Mon, 10 Dec 2007 11:44:50 GMT

Hi sana,

Thanks for the reply.

So now part1 and part3 of my question is answered.

Still i would like to know if which field tells me the Quanitity of Material deleted,

as i have to display the Quantity of material deleted and its value using ALV.

Can i use the Fields MENGE & VKWRT from table ISEG for the same???