topic Re: PROB INNER JOIN in Application Development and Automation Discussions

PROB INNER JOIN

Former Member — Tue, 19 Dec 2006 09:31:33 GMT

BELOW IS MY PROGRAM. AND I WANT TO SHOW THE WHOLE DATA OF ALL THE TABLE.SO HOW I WILL DEFINE TABLE IN DATA STATEMENT AND IN SELECT STATEMENT.

PLZ IT'S URGENT

TAHNKS..

SAM

&----

*& Report ZTEMP_JOIN

&----

REPORT ZTEMP_JOIN.

TABLES: MARA,MARC,MARD,MSEG, MAST.

SELECT-OPTIONS: SAM FOR MARA-MATNR.

DATA: BEGIN OF ITAB OCCURS 0,

MATNR LIKE MARA-MATNR,

ERSDA LIKE MARA-ERSDA,

ERNAM LIKE MARA-ERNAM,

MTART LIKE MARA-MTART,

WERKS LIKE MARC-WERKS,

PSTAT LIKE MARC-PSTAT,

LVORM LIKE MARC-LVORM,

LFGJA LIKE MARD-LFGJA,

MBLNR LIKE MSEG-MBLNR,

MJAHR LIKE MSEG-MJAHR,

BWART LIKE MSEG-BWART,

STLAN LIKE MAST-STLAN,

STLAL LIKE MAST-STLAL,

STLNR LIKE MAST-STLNR,

END OF ITAB.

SELECT MA~MATNR MA~ERSDA MA~ERNAM MA~MTART MC~WERKS MC~PSTAT MC~LVORM MD~LFGJA MG~MBLNR MG~MJAHR MG~BWART MT~STLAN MT~STLAL MT~STLNR

INTO TABLE ITAB FROM MARA AS MA INNER JOIN MARC AS MC ON

MA~MATNR = MC~MATNR

INNER JOIN MARD AS MD ON

MC~MATNR = MD~MATNR

INNER JOIN MSEG AS MG ON

MD~MATNR = MG~MATNR

INNER JOIN MAST AS MT ON

MG~MATNR = MT~MATNR

WHERE MA~MATNR IN SAM.

LOOP AT ITAB.

WRITE:/ ITAB-MATNR,ITAB-ERSDA,ITAB-ERNAM,ITAB-MTART,ITAB-WERKS, ITAB-PSTAT, ITAB-LVORM, ITAB-LFGJA,

ITAB-MBLNR, ITAB-MJAHR, ITAB-BWART, ITAB-STLAN, ITAB-STLAL, ITAB-STLNR.

ENDLOOP.

*START-OF-SELECTION.

WRITE: / 'line 1'.
WRITE: / 'line 2'.
WRITE: / 'line 3'.

TOP-OF-PAGE.

WRITE: / 'MM FIVE TABLES INNNER JOIN'.

ULINE.

TOP-OF-PAGE.

WRITE: /1(109) sy-uline.

WRITE: /1 '|', 2(18) 'MATNR-MATNR' COLOR 1,

20 '|', 21(8) 'ERSDA' COLOR 2,

29 '|', 30(12) 'ERNAM-ERNAM' COLOR 3,

42 '|', 43(4) 'MTART' COLOR 4,

47 '|', 48(4) 'WERKS' COLOR 5,

52 '|', 53(15) 'PSTAT-PSTAT' COLOR 6,

68 '|', 69(1) 'LVORM' COLOR 7,

70 '|', 71(4) 'LFGJA' COLOR 1,

75 '|', 76(10) 'MBLNR' COLOR 2,

86 '|', 87(4) 'MJAHR' COLOR 3,

91 '|', 92(3) 'BWART' COLOR 4,

95 '|', 96(1) 'STLAN' COLOR 5,

97 '|', 98(2) 'STLAL' COLOR 6,

100 '|', 101(8) 'STLNR' COLOR 7,

109 '|'.

WRITE: /1(109) sy-uline.

Re: PROB INNER JOIN

Former Member — Tue, 19 Dec 2006 09:35:17 GMT

Hi Sam,

Refer sample code when using Multiple INNER JOIN.

SELECT T1~PERNR

T7~ZDSILONR

T7~ZDTEAMNR

T1~AUFNR

T1~GMNGA

T2~MATNR

T2~CHARG

T4~ARBPL

T8~AUART

INTO TABLE I_ITAB1

FROM ( ( ( ( AFRU AS T1 INNER JOIN AFPO AS T2

ON T1_{WERKS = T2}DWERK AND

T1_{AUFNR = T2}AUFNR )

INNER JOIN S022 AS T4 ON T4_{AUFNR = T1}AUFNR AND

T4~VORNR = C_VORNR )

INNER JOIN ZTK_ORDRSILOTEAM AS T7 ON T7_{ZDAUFNR = T1}AUFNR )

INNER JOIN AUFK AS T8 ON T8_{AUFNR = T2}AUFNR )

WHERE T1~WERKS IN S_PLANT AND

T1~BUDAT IN S_START AND

T1~PERNR IN S_SHIFT AND

T7~ZDTEAMNR IN S_TEAM AND

T2~MATNR IN S_ITEM AND

T1_{AUFNR = T8}AUFNR AND

T4~ARBPL IN S_WORK AND

T8~AUART IN (C_ZCES,C_ZCRS,C_ZCEX,

C_ZCFT,C_ZCFS,C_ZCFX).

Reward points if this Helps.

Manish

Re: PROB INNER JOIN

Former Member — Tue, 19 Dec 2006 09:38:56 GMT

Hello SAM,

You need to use joins when the record in the database table is less.

If the data is more u need to use the FOR ALL ENTRIES.

DATA: it_mara like mara occurs 0 with header line,

it_marc like marc occurs 0 with header line,

it_mard like mard occurs 0 with header line,

it_mseg like mseg occurs 0 with header line.

select * from mara into table it_mara where matnr in sam.

if not it_mara[] is initial.

select * from marc into table it_marc for all entries in it_mara where matnr = it_mara-matnr.

select * from mard into table it_mard for all entries in it_mara where matnr = it_mara-matnr.

select * from mseg into table it_mseg for all entries in it_mara where matnr = it_mara-matnr.

endif.

Loop at it_mara.

read it_marc..

read it_mard..

read it_mseg..

append itab.

endloop.

If useful reward.

vasanth

Re: PROB INNER JOIN

Former Member — Tue, 19 Dec 2006 09:43:46 GMT

I really did not get the issue ...what is not working in your code !!

But looking at the same I would suggest not to join so many tables in a single query as it would be time consuming and non-efficient. It would be better to split the same in to different selects.

Regards

Anurag

Re: PROB INNER JOIN

Former Member — Tue, 19 Dec 2006 09:44:49 GMT

Hi Sam,

When Inner Joins are used, Data will be displayed only where all the relevant tables have the same data on which it is being joined.

i.e. <b>MATNR used in the select option should be present in all the tables being selected from.</b>

Trust this helps. Reward if helpful.

Regards,

Biju

Re: PROB INNER JOIN

Former Member — Tue, 19 Dec 2006 09:45:53 GMT

Hi sam,

use the select statement like

SELECT MA_{MATNR MA}ERSDA MA_{ERNAM MA}MTART MC_{WERKS MC}PSTAT MC_{LVORM MD}LFGJA MG_{MBLNR MG}MJAHR MG_{BWART MT}STLAN MT_{STLAL MT}STLNR

INTO TABLE ITAB FROM ( ( ( MARA AS MA INNER JOIN MARC AS MC ON

MA_{MATNR = MC}MATNR ) INNER JOIN MARD AS MD ON

MC_{MATNR = MD}MATNR ) INNER JOIN MSEG AS MG ON

MD_{MATNR = MG}MATNR ) INNER JOIN MAST AS MT ON

MG_{MATNR = MT}MATNR )

WHERE MA~MATNR IN SAM.

But i suggest instead of creating innerjoin so many times better go for database view in se11..

create a database view using all these tables. database view is like any table which is a collection of data from various tables.

this view you can use as any database table in your select query,

this enhances your performance.you should always avoid writing innerjoins for more than two tables.

regards,

pankaj singh

Re: PROB INNER JOIN

Former Member — Tue, 19 Dec 2006 09:46:59 GMT

FOR ALL ENTRIES IN is suggested if the tables are having huge volume of data.