topic Re: INNER JOIN in Application Development and Automation Discussions

INNER JOIN

Former Member — Fri, 28 Mar 2008 04:37:31 GMT

HI Frds

I have inner join three tables MARA, MARD, MKPF .In DEV its working correctly ,while going for bulk datas it will work correct.?

Pls check my select statement and give me some tips.

SELECT MSEG_{MBLNR MSEG}MJAHR MSEG_{MATNR MSEG}WERKS MSEG~BUKRS

MSEG_{LGORT MSEG}LIFNR MSEG_{MENGE MSEG}MEINS MSEG~EBELN

MSEG_{EBELP MSEG}INSMK

MARD_{MATNR MARD}INSME

MKPF_{MBLNR MKPF}MJAHR MKPF~BUDAT

INTO TABLE IMSEG_MARD FROM MSEG

INNER JOIN MARD ON MARD_{MATNR = MSEG}MATNR

INNER JOIN MKPF ON MKPF_{MBLNR = MSEG}MBLNR

WHERE MSEG~WERKS IN PLANT AND

MSEG~BUKRS IN COMPANY AND

MSEG~LIFNR IN VENDOR AND

BUDAT IN GR_DATE AND

MSEG~INSMK EQ 'X' AND

MARD~INSME <> '0.000'.

CLEAR : IMSEG_MARD.

thanks

Pari

Re: INNER JOIN

Former Member — Fri, 28 Mar 2008 04:43:03 GMT

hi,

it will work fine even though you have bunch of data in DEV/Quality/Prod.

if you have more than 3 tables then its better to use for all entries with respect to performance point of view.

Chandra

Re: INNER JOIN

ak_upadhyay — Fri, 28 Mar 2008 04:46:47 GMT

Hi,

It will work.

Regards

Re: INNER JOIN

Former Member — Fri, 28 Mar 2008 04:48:37 GMT

hi,

SELECT MSEG_{MBLNR MSEG}MJAHR MSEG_{MATNR MSEG}WERKS MSEG~BUKRS

MSEG_{LGORT MSEG}LIFNR MSEG_{MENGE MSEG}MEINS MSEG~EBELN

MSEG_{EBELP MSEG}INSMK

MARD_{MATNR MARD}INSME

MKPF_{MBLNR MKPF}MJAHR MKPF~BUDAT

INTO TABLE IMSEG_MARD FROM MSEG

INNER JOIN MARD ON MSEG_{MATNR = MARD}MATNR

INNER JOIN MKPF ON MSEG_{MBLNR = MKPF}MBLNR

WHERE MSEG~WERKS IN PLANT AND

MSEG~BUKRS IN COMPANY AND

MSEG~LIFNR IN VENDOR AND

BUDAT IN GR_DATE AND

MSEG~INSMK EQ 'X' AND

MARD~INSME EQ '0.000'.

CLEAR : IMSEG_MARD .

Re: INNER JOIN

Former Member — Fri, 28 Mar 2008 04:51:30 GMT

HI,

Do avoid Inner Join.please use For all Entries.

Regards,

S.Nehru.

Re: INNER JOIN

abapdeveloper20 — Fri, 28 Mar 2008 04:52:22 GMT

Hi,

1.Inorder to improve the performance of your query , make sure that the order of fields in the "WHERE" should follow the order of fields in the DDIC TABLES

2. use SQL trace to point out the performance of your query while executing with data.

reward if helpful.

~_Lakshmiraj~

Re: INNER JOIN

Former Member — Fri, 28 Mar 2008 04:55:33 GMT

Hi RAJA

You havent mwentioned INTO CORRESPONDIN FIELDS OF

and no BRACKETS MENTIONED which i've made BOLD..

SELECT MSEG_{MBLNR MSEG}MJAHR MSEG_{MATNR MSEG}WERKS MSEG~BUKRS

MSEG_{LGORT MSEG}LIFNR MSEG_{MENGE MSEG}MEINS MSEG~EBELN

MSEG_{EBELP MSEG}INSMK

MARD_{MATNR MARD}INSME

MKPF_{MBLNR MKPF}MJAHR MKPF~BUDAT

INTO CORRESPONDING FIELDS OF TABLE IMSEG_MARD FROM ((MSEG

INNER JOIN MARD ON MARD_{MATNR = MSEG}MATNR)

INNER JOIN MKPF ON MKPF_{MBLNR = MSEG}MBLNR)

WHERE MSEG~WERKS IN PLANT AND

MSEG~BUKRS IN COMPANY AND

MSEG~LIFNR IN VENDOR AND

BUDAT IN GR_DATE AND

MSEG~INSMK EQ 'X' AND

MARD~INSME '0.000'.

CLEAR : IMSEG_MARD.

Regards

Lakshman

Re: INNER JOIN

dhruv_shah3 — Fri, 28 Mar 2008 05:02:07 GMT

Hi,

If you use it like below then it will be more efficient.

SELECT MBLNR MJAHR MATNR WERKS BUKRS

LGORT LIFNR MENGE MEINS EBELN

EBELP INSMK

FROM MSEG INTO CORRESPONDING FIELDS OF TABLE IT_MSEG.

IF IT_MSEG IS NOT INITIAL.

SELECT

MATNR INSME

FROM MARD

INTO CORRESPONDING FIELDS OF TABLE IT_MARD

FOR ALL ENTRIES IN IT_MSEG

WHERE MATNR = IT_MSEG-MATNR.

ENDIF.

IF IT_MSEG_MARD IS NOT INITIAL.

SELECT

MBLNR MJAHR BUDAT

FROM MKPF

INTO CORRESPONDING FIELDS OF TABLE

FOR ALL ENTRIES IN IT_MSEG_MARD

WHERE MBLNR = IT_MSEG_MARD-MBLNR.

AND MATNR = IT_MSEG_MARD-MATNR.

AND

MSEG-WERKS IN PLANT AND

MSEG-BUKRS IN COMPANY AND

MSEG-LIFNR IN VENDOR AND

BUDAT IN GR_DATE AND

MSEG-INSMK EQ 'X' AND

MARD-INSME EQ '0.000'.

ENDIF.

CLEAR : ITMSEG_MARD.

HTH

Regards,

Dhruv Shah