topic inner join inside a internal table loop in Application Development and Automation Discussions

inner join inside a internal table loop

Former Member — Tue, 24 Apr 2007 07:27:39 GMT

Hello to all,

I have this internal table in which i have selected some records for particular coulms. For the rest of the colums i need to join 3 tables for the data. I had in mind, of looping the internal table and then having a inner join query and modifying the internal table(all inside the loop) Just wanted to know, whether its a qood practice performance wise ? IF not, what are the alternatives ..

Thanks,

Shehryar Dahar

Re: inner join inside a internal table loop

Former Member — Tue, 24 Apr 2007 07:32:29 GMT

Hi,

Doing joins on tables is not a good practice as it slow down ur program execution in the production server.Better u use internal tables for that.Their is no restriction as how many internal tables u are using for that.But before using loop at internal table always write check not itab[] is initial. where itab is ur internal table name upon which u want to loop at.

plz reward points if helpful.

Thanks

Chinmay

Re: inner join inside a internal table loop

Former Member — Tue, 24 Apr 2007 07:51:04 GMT

Hi Shehryar,

Having a select query that too a join on three tables inside a loop is not a good idea at all. This way you will be querying your database as many times as you have rows in the internal table. This will be a big performance issue. To avoid this you can use a single select statement and use the addition FOR ALL ENTRIES IN <internal table>. This way you will have only one query to the database. Also dont forget to check if your internal table contains any entries before doing the select.

Hope this help!

Regards,

Saurabh

Re: inner join inside a internal table loop

Former Member — Tue, 24 Apr 2007 08:01:33 GMT

good

i dont think there is any need to loop the internal table,if you want to join the internal tables than you can directly join those three tables using the JOIN statement.

thanks

mrutyun^

Re: inner join inside a internal table loop

Former Member — Tue, 24 Apr 2007 08:02:53 GMT

Hello,

But then i will have to use 'SELECT SINGLE' and 'FOR ALL ENTRIES' twice for retrieving data from 3 tables....right ?

Regards,

Shehryar Dahar

Re: inner join inside a internal table loop

Former Member — Tue, 24 Apr 2007 08:04:45 GMT

hi,

first use an single select stm for that particular column in the table after that use an inner join on the 3 table using for all enteries this wil solve ur prob i guess

reward if helpful

ravi

Re: inner join inside a internal table loop

Former Member — Tue, 24 Apr 2007 08:11:59 GMT

shehryar,

say You have internal table ITAB.Already some columns are updated in internal table.say A,B,C,D.

To fill rest of the columns(E,F,G) you need inter join.

write the inner join of the table for all entries of itab where A eq itab-A.

LOOP ITAB.

READ TABLE ITAB1 WITH KEY X = ITAB-X.

IF SY-SUBRC EQ 0.

move ITAB record to I_FINAL.

move itab1 records to i_final.

append i_final.

clear i_final.

ENDIF.

ENDLOOP.

Don't forget to reward if useful...