topic Re: Internal table and Database table. in Application Development and Automation Discussions

Internal table and Database table.

Former Member — Tue, 06 Mar 2007 08:28:18 GMT

Hi,

For example, i have an ITAB which contains field 'LIFNR' and full of data in this ITAB.

How to get the the corresponding data of 'ADRNR' from table 'LFA1' when ITAB-LIFNR = LFA1-LIFNR??

I dont know whether you got me or not.

what i want is like this:

select t1~adrnr
         from lfa1 as t1 inner join itab as t2 
         on t1~lifnr = t2~lifnr.

But the problem is that ITAB is an internal table, it cant be done like that.

I am not familiar with ABAP satements, Is there other way to complete this??

Thanks and best regards,

hoo

Re: Internal table and Database table.

Former Member — Tue, 06 Mar 2007 08:30:59 GMT

Hi,

use for all entries syntax.

select adrnr

from lfa1

into table it_lfa1

for all entries in iatb

where lifnr = itab-lifnr.

Regards,

Ravi

Re: Internal table and Database table.

Former Member — Tue, 06 Mar 2007 08:33:57 GMT

loop at itab into wa_itab.
  select single ADRNR from LFA1 into itab-adrnr where lifnr eq wa_itab-lifnr.
  modify itab from wa_itab.
endloop.

Re: Internal table and Database table.

Former Member — Tue, 06 Mar 2007 08:34:03 GMT

Hi,

Use the following syntax.

if itab[] is not initial.

Select LIFNR

ADRNR

from lfa1

into table itab2

for all entries in ITAB

where lifnr = itab-lifnr.

endif.

Regards.. Jaison.

Re: Internal table and Database table.

Former Member — Tue, 06 Mar 2007 08:34:43 GMT

Hi Ravi,

May i code like this:

loop at itab.

select single adrnr into table itab

where lifnr = itab-lifnr.

append itab.

clear itab.

endloop.

Thanks,

hoo

Re: Internal table and Database table.

Former Member — Tue, 06 Mar 2007 08:36:10 GMT

Hi,

You can do that, but as per standards, pefrormance will be poor if you use a select sttatement inside a loop.

use for all entries instead.

Regards,

Ravi

Re: Internal table and Database table.

Former Member — Tue, 06 Mar 2007 08:39:45 GMT

Great!!!

One thing i wanna to know is that which one have better <b>performance</b>??

Option1:

loop at itab into wa_itab.
  select single ADRNR from LFA1 into itab-adrnr where lifnr eq wa_itab-lifnr.
  modify itab from wa_itab.
endloop.

Option2:

Select LIFNR 
ADRNR
from lfa1
into table itab2
for all entries in ITAB
where lifnr = itab-lifnr.

Coz the itab has huge records.

Thanks in advance.

hoo

Re: Internal table and Database table.

Former Member — Tue, 06 Mar 2007 08:42:00 GMT

hii Hoo,

try this :

data : begin of itab1 occurs 0 with header line,

LIFNR like LFA1-LIFNR,

ADRNR like LFA1-ADRND,

end of itab1,

begin of itab2 occurs 0 with header line,

LIFNR like LFA1-LIFNR,

ADRNR like LFA1-ADRND,

end of itab2.

*****suppose now u have got LIFNR in ur itab1.

if not itab1[] is initial.

select LIFNR ADRNR

from LFA1

into corresponding fields of table itab2

for all entries in itab1

where lifnr = itab1-lifnr.

endif.

if not itab2[] is initial.

itab1[] = itab2[].

endif.

****now u have got the data u want in itab1 as well as itab2.

Reward useful answers with points !!

Regards,

Tejas

Re: Internal table and Database table.

Former Member — Tue, 06 Mar 2007 08:42:51 GMT

Hi,

For all entries will have better performance.

and remove the duplicate entries from itab comparing lifnr.

regards,

madhu

Re: Internal table and Database table.

Former Member — Tue, 06 Mar 2007 08:44:37 GMT

going by theory Option 2 will have have better performance,

so use this..

Select LIFNR 
ADRNR
from lfa1
into table itab2
for all entries in ITAB
where lifnr = itab-lifnr.

sort itab2 by lifnr adrnr.

loop at itab into wa_itab.
  read table itab2 with key lifnr eq  wa_itab-lifnr binary search.
   wa_itab-adrnr = itab2-adrnr .
  modify itab from wa_itab.
endloop.

Re: Internal table and Database table.

Former Member — Tue, 06 Mar 2007 08:44:56 GMT

You should not use the JOIN statement in the select clause for internal table but you can use this statement for two databse tables.

The following staement will help u.

select * from lfa1 into table i........

for all entries in itab1 where lifnr = itab1-lifnr.

Hope u got it.