topic Re: Inner join help in Application Development and Automation Discussions

Inner join help

Former Member — Wed, 30 Jul 2008 05:54:32 GMT

Hello Friends,

I am Debugging one report and I want to know the meaning of following Query, That what this querri is doing and what result will come, Please guide me as soon as possible.........

SELECT a_{budat b}matnr b_{werks c}mtart SUM( b~menge ) AS xmenge

INTO (budat, matnr, werks, mtart, menge)

FROM ( ( mkpf AS a INNER JOIN mseg AS b ON a_{mblnr = b}mblnr )

INNER JOIN mara AS c ON b_{matnr = c}matnr )

WHERE a~budat IN p_budat

AND b~bwart = '101'

AND ( b_{lgort = 'FGSL' )"OR b}lgort = 'JWSL' OR b~lgort = 'SCRL' )

AND a~mjahr = mjahr

GROUP BY a_{budat b}matnr b_{werks c}mtart

ORDER BY a_{budat b}matnr b_{werks c}mtart.

itab-budat = budat.

itab-matnr = matnr.

itab-werks = werks.

itab-mtart = mtart.

itab-menge = menge.

APPEND itab.

CLEAR: itab, budat, matnr, werks, mtart, menge.

ENDSELECT.

Re: Inner join help

Former Member — Wed, 30 Jul 2008 06:02:16 GMT

Hi Shelly,

This query is an Inner join among 3 table MARA,MKPF and MSEG on field MKPF -budat MSEG-matnr MSEG-werks MARA-mtart.

And sums up on field MESG-menge.

After that Where condition,GROUP BY and ORDER BY are self explanetory.

Finaly the required fields are taken into internal table itab, which is an INTERNAL TABLE with header line so have same name for work area and internal table.

Finaly the buffer is cleared with CLEAR keyword.

Since you are Anlysing it, let me tell you

1. Using iNNER JOIN among 3 tables leads to major performance ISSUE

and

2. Using of Internal table with header line is OBSOLATE in SAP.

Regards,

Anirban

Re: Inner join help

Former Member — Wed, 30 Jul 2008 06:07:03 GMT

Hi Friend,

This select query actually storing total goods receipts (101) quantity (menge) against all material (matnr) within specified date (budat) and storage location (lgort) shoule be FGSL/JWSL/SCRL, material document year (MJHAR) should be provdie value mjhar, in internal table itab.

Note: this select query consume a lot of memory area.

Hope it will help you.

Regards

Krishnendu

Re: Inner join help

Former Member — Wed, 30 Jul 2008 06:29:33 GMT

Dear Sir,

Can You please guide me how can I write it in efficient way...

Warm Regards

Shelly Malik

Re: Inner join help

Former Member — Wed, 30 Jul 2008 06:30:18 GMT

Dear sir,

Can you please guide me how can I write it in effecient way????

Re: Inner join help

Former Member — Wed, 30 Jul 2008 06:31:28 GMT

Dear Sir,

Can You please guide me how can I write it in efficient way...

Warm Regards

Shelly Malik

Re: Inner join help

Former Member — Wed, 30 Jul 2008 06:35:54 GMT

Hi shelly,

1.Instead of use Inner join use FOR ALL ENTRIES. For detail concept check

http://help.sap.com/saphelp_nw04/helpdata/en/fc/eb3a1f358411d1829f0000e829fbfe/content.htm

and

2. Use Internal table without header line for your code.

All the best,

Anirban

Re: Inner join help

Former Member — Wed, 30 Jul 2008 06:53:02 GMT

Hi Friend,

Ohh! i will guide you to write in efficeien way:

1. SELECT data from MKPF, MSEG and MARA using inner join into an internal table. Where condition will be like as it is.

You can not use SUM(MENGE) use only MENGE

2. Use group by, order by with those clause on that internal table.

3. Loop that internal table and use AT END OF BUDAT. SUM. END AT it will sum as required. Within that AT END AT pass values to an work are like itab. APPEND itab.

Check the work are before appending, if you are not getting all value, pass to and local work area before AT.

\[removed by moderator\]

Regards

Krishnendu

Edited by: Jan Stallkamp on Jul 30, 2008 4:24 PM

Re: Inner join help

Former Member — Wed, 30 Jul 2008 07:00:36 GMT

Hi Shelly,

Please check this link

[JOINS vs. FOR ALL ENTRIES - Which Performs Better?|https://www.sdn.sap.com/irj/sdn/weblogs?blog=/pub/wlg/6050] [original link is broken] [original link is broken] [original link is broken];

Best regards,

raam