INNER JOINS

[Former Member]

Hi Folks,

i am using the inner joins to get the required data from different tables and keeping it in an internal table . Then i am looping at this internal table and retrieving required fields from infotypes IT0000,IT0001,IT0002,IT0006.

here are my select statements.

SELECT pa0000~pernr INTO TABLE it_pernr FROM PA0000

INNER JOIN PA0001 on PA0000_{pernr = PA0001}pernr

INNER JOIN PA0006 on PA0000_{pernr = PA0006}pernr

INNER JOIN T500p on PA0001_{werks = T500p}persa

WHERE pa0000_{begda LE sy-datum and pa0000}endda GE

sy-datum and pa0000_{pernr in s_pernr and pa0001}persg = '1' or '2' or '3' and t500p_{molga = '10' and pa0006}land1 = 'US' and pa0006~subty =

'1' group by pa0000~pernr

Next joins.

SELECT pa0001_{pernr t5uee}eeoct t5uee_{eeotx INTO TABLE it_t5u13 FROM pa0001 INNER JOIN t5u13 ON pa0001}stell = t5u13_{stell INNER JOIN t5uee on t5u13}eeoct = t5uee_{eeoct FOR ALL ENTRIES IN it_pernr WHERE pa0001}pernr = it_pernr-pernr.

Please suggest me if there is any other approach . Let me know by using the joins has performance issues .

Thanks,

bluemax.

[Former Member]

Hi Folks,

i am using the inner joins to get the required data from different tables and keeping it in an internal table . Then i am looping at this internal table and retrieving required fields from infotypes IT0000,IT0001,IT0002,IT0006.

here are my select statements.

SELECT pa0000~pernr INTO TABLE it_pernr FROM PA0000

INNER JOIN PA0001 on PA0000_{pernr = PA0001}pernr

INNER JOIN PA0006 on PA0000_{pernr = PA0006}pernr

INNER JOIN T500p on PA0001_{werks = T500p}persa

WHERE pa0000_{begda LE sy-datum and pa0000}endda GE

sy-datum and pa0000_{pernr in s_pernr and pa0001}persg = '1' or '2' or '3' and t500p_{molga = '10' and pa0006}land1 = 'US' and pa0006~subty =

'1' group by pa0000~pernr

Next joins.

SELECT pa0001_{pernr t5uee}eeoct t5uee_{eeotx INTO TABLE it_t5u13 FROM pa0001 INNER JOIN t5u13 ON pa0001}stell = t5u13_{stell INNER JOIN t5uee on t5u13}eeoct = t5uee_{eeoct FOR ALL ENTRIES IN it_pernr WHERE pa0001}pernr = it_pernr-pernr.

Please suggest me if there is any other approach . Let me know by using the joins has performance issues .

Thanks,

bluemax.

[Former Member]

Hi

I think the other way could be creating View.By using view It would be rather easy and optimised performance.

Regards.

vikas.

[Laxmana_Appana_]

Hi,

yes, joins on multiple tables takes time to fetch the data , instead of joins use 'FOR ALL ENTRIES.... ' option in selects .

Ex: if you want to retrieve data from tables A,B,C.

first select data from A into internal table I1, and then select data from B using for all entries in the internal table I1 into I2....etc.

at the end use final internal table and read data from I1,T2..... and pass data to final internal table.

Regards

appana

[former_member184495]

hi,

as suggested by our friends , u can use 'For all Entries',

but in your code, i feel the 'group by' is not reqd, since u rn't using any function in the select statement.

i may be wrong,

cheers,

Aditya.

[Former Member]

hi bluemax,

u can use 'For All Entries' statement in ur program instead of Inner join.

select data from first databast table into one internal table,

then select data from other database table into another internal table.

like that;

select <fields> from <DBtable> into <itab2>(another internal table) for all entries in <itab1>(first internal table).

do same for others also.

Hope it will help u.

Naveen

[aris_hidalgo]

As others have said, the FOR ALL ENTRIES statement is faster compared to using JOIN statement. Below is an example:

*select records based from entries in selection screen

SELECT tplnr anlnr iloan swerk stort FROM iloa

INTO TABLE it_iloa

WHERE tplnr IN so_tplnr

AND stort IN so_stand

AND anlnr IN so_anln1

AND owner EQ space.

*if itab it_iloa does not contain records, exit program

CHECK NOT it_iloa[] IS INITIAL.

*select records from table t499s based from entries in it_iloa

SELECT werks stand ktext FROM t499s

INTO TABLE it_t499s

FOR ALL ENTRIES IN it_iloa

WHERE werks EQ it_iloa-swerk

AND stand EQ it_iloa-stort.

*select records from table equz based from entries in it_iloa

SELECT equnr hequi iloan iwerk FROM equz

INTO TABLE it_equz

FOR ALL ENTRIES IN it_iloa

WHERE iloan EQ it_iloa-iloan.

CHECK NOT it_equz[] IS INITIAL.

*select records from table iflo based from entries in it_iloa

SELECT tplnr pltxt FROM iflo

INTO TABLE it_iflo

FOR ALL ENTRIES IN it_iloa

WHERE tplnr EQ it_iloa-tplnr.

SELECT DISTINCT iwerk FROM equz

INTO TABLE it_equz2

FOR ALL ENTRIES IN it_equz

WHERE iloan EQ it_equz-iloan.

P.S. Please award points if found helpful.