problem creating extract file

[Former Member]

I am stuck on a process that I have to write and was hoping that someone in here could give me some solutions or some starting points. I have to create a vendor extract containing fields from tables LFA1, LFB1, LFBK, LFBW, LFM1, LFZA, ADR6, and ADRC. I had originally coded the the program with a massive select and using inner and outer joins but this was not selecting all of the data that I needed as well as I was told that the join process is a performance problem.I have coded a select statement for each table and created separate work tables. the problem comes in when I have to create the extract file. I am using the LFA1 table entries as my starting point but I am not sure how to create the extract entries when there are multiple entries in the other tables. here is an example and it may sound a little confusing. I have one vendor in the LFA1 table that has 2 entries in the LFB1 table. It also has 2 entries in the LFBK table. My extract file needs to have two entries in the file. I am thinking that I have to write the data frommthe LFA1 table to a hold table, then select the data from the LFB1 table using the data from the hold table and create a second hold table that has the two entries from LFB1. I then have to select the data from LABK table using the data from the second hold table and create a third hold table that has the data from all three tables. I would do this process for every table that I have to read until I get to the end and then I would have a final hold table that contains all of the records with all of the data that I need. This sounds a little cumbersome and I think that this would work but I wiould like to know if there is a more simplistic approach to this.

thanks in advance for the help

[Former Member]

Hi,

You can use joins for the related tables provided you know the correct key fields and the data should exist in all the tables.

in this case you can join the tables LFA1, LFB1, LFBK, LFBW, LFM1, LFZA with a single join statement as the LIFNR is the key field in all the above tables.

then take ADRNR filed and write a sepearate join for the tables ADR6 and ADRC for all th entries in first ITAB. Then loop the first ITAB and read the second ITAB and put the entries into a final internal tables and display the data.

reward if useful

regards,

Anji

[Former Member]

I had originally coded with the joins but I was told by the business that there could a possibly that there could be a vendor in lfa1 that does not have a record in the lfb1 table. with the join, I would not select this record. I had to use a inner join for this because one of the selection criterea is the company code and the joind had to be a inner join for this to work. that is whay I switched it to single selects for all of the tables.