same field from INNER JOIN tables

[Former Member]

Hi,

I have to tables T1 and T2. Field F1 exists in both tables with the same name. I use the following statement to populate internal table. Some data is from T1 and the rest comes from T2. As to filed F1, sometimes T2-F1 is initial and T1-F1 is always filled. F1 of the internal table should come from T1. How can I optimize my code to retrieve the correct data rather than list every fields after SELECT? There are so many fields to populate so I want to use SELECT * rather than to list each of them.

SELECT *

     INTO CORRESPONDING TABLE OF lt_tab

   FROM T1 INNER JOIN T2 ON ... .

Thanks,

ts

[Former Member]

I had a similar issue with EKKO-AEDAT and EKPO-AEDAT, to name a specific example.

By my observation, the values from the table that appears later in the join statement will make it into the result set, so try switching T1 and T2 around and see if there is an improvement.

However, I don't know how robust this is, means whether this logic would always be valid for all database systems. You can certainly not go wrong by listing the single columns e.g. EKKO~AEDAT, although you said you have a lot of them.

Thomas

[Former Member]

Hi, if I understood your query correctly, try using "LEFT OUTER JOIN". More info on select queries:ABAP Keyword Documentation

[Former Member]

Hi Maju,

Thank you for your reply but the LEFT OUTER JOIN doesn't work.

[ThomasZloch]

So did switching the position of the tables in the join statement work?

[Former Member]

Yes, thanks. It works.

[Former Member]

You need to use outer join, not inner join, if you want the values in T1 even if F1 is initial in T2.

select * into corresponding fields of table lt_tab from T1 left outer join T2 on.. "condition.

Regarding using *, make sure that all the required field are there in the target table lt_tab.

If you are looking for performance, its always better to list the fields instead of writing '*' when you are using into corresponding fields of table.

[ThomasZloch]

No, there is no performance difference:

Thomas

[Former Member]

Hi Susmitha,

Thanks for your reply but the left outer doesn't work.

[ThomasZloch]

I had a similar issue with EKKO-AEDAT and EKPO-AEDAT, to name a specific example.

By my observation, the values from the table that appears later in the join statement will make it into the result set, so try switching T1 and T2 around and see if there is an improvement.

However, I don't know how robust this is, means whether this logic would always be valid for all database systems. You can certainly not go wrong by listing the single columns e.g. EKKO~AEDAT, although you said you have a lot of them.

Thomas

[Former Member]

Try below sample code

select t1~f1

          f2

          f3...

          from <Table1> as t1 join <table2> as t2 on t1~f1 = t2~f1  into corresponding fields of table                     <internal table>

          where <condition>.

t1-->alias name for table1.

t2-->alias name for table2.

Thanks and Regards,

Rajkumar Parthipan.