topic Re: While joining two tables in Application Development and Automation Discussions

While joining two tables

Former Member — Wed, 19 Mar 2008 13:20:22 GMT

Hi all,

Assume that someone needs to join two tables called PA0002 and PA0008 with keyword PERNR and

that person needs to read the data of PA0008 aedtm's max.

That is there are many records based on the aedtm and the user wants to read the last date update and wants to use this in join, not separately.

Any idea?

Thanks.

deniz

Re: While joining two tables

Former Member — Wed, 19 Mar 2008 13:24:28 GMT

hi in the sort use this..

sort itab by pernr begda descending.

regards,

venkat.

Re: While joining two tables

Former Member — Wed, 19 Mar 2008 13:30:29 GMT

hi use this..

report.

tables:pa0002,pa0008.

data: begin of itab occurs 0,

pernr like pa0002-pernr,

vorna like pa0002-vorna,

nachn like pa0002-nachn,

begda like pa0002-begda,

endda like pa0002-endda,

end of itab.

data: begin of itab1 occurs 0,

pernr like pa0008-pernr,

begda like pa0008-begda,

stvor like pa0008-stvor,

ansal like pa0008-ansal,

end of itab1.

data:begin of final occurs 0,

pernr like pa0002-pernr,

vorna like pa0002-vorna,

nachn like pa0002-nachn,

begda like pa0008-begda,

stvor like pa0008-stvor,

ansal like pa0008-ansal,

end of final.

select-options:s_pernr for pa0002-pernr.

start-of-selection.

select pernr

vorna

nachn

begda

endda

from pa0002

into table itab

where pernr in s_pernr.

sort itab by pernr begda descending.

select pernr

begda

stvor

ansal

from pa0008

into table itab1

for all entries in itab

where pernr = itab-pernr.

sort itab1 by pernr begda descending.

loop at itab.

final-pernr = itab-pernr.

final-vorna = itab-vorna.

final-nachn = itab-nachn.

read table itab1 with key pernr = itab-pernr.

final-begda = itab1-begda.

final-stvor = itab1-stvor.

final-ansal = itab1-ansal.

append final.

clear final.

endloop.

loop at final.

write:final-pernr ,

final-vorna ,

final-nachn ,

final-begda ,

final-stvor ,

final-ansal .

endloop.

regards,

venkat.

Re: While joining two tables

Former Member — Wed, 19 Mar 2008 13:35:47 GMT

If all you need is the latest date, you could simply do this. Using a MAX function will minimize your database IO and network traffic.

The keys of the two tables contain more than pernr. You may need to expand on your join.


data: g_t_PA0008 type TABLE OF PA0008.

select max( p8~aedtm )  as max_aedtm
  into CORRESPONDING FIELDS OF TABLE g_t_PA0008
from PA0008 as p8
  inner join PA0002 as p2
    on p2~pernr = p8~pernr.