INNER JOIN

[Former Member]

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

[Former Member]

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

[ak_upadhyay]

Hi,

It will work.

Regards

AK

[Former Member]

hi,

do

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 .

[Former Member]

HI,

Do avoid Inner Join.please use For all Entries.

Regards,

S.Nehru.

[abapdeveloper20]

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~

[Former Member]

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

[dhruv_shah3]

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