topic Re: same field from INNER JOIN tables in Application Development and Automation Discussions

same field from INNER JOIN tables

Former Member — Mon, 10 Feb 2014 10:07:46 GMT

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,

Re: same field from INNER JOIN tables

Former Member — Mon, 10 Feb 2014 10:28:23 GMT

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

Re: same field from INNER JOIN tables

Former Member — Mon, 10 Feb 2014 10:37:04 GMT

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.

Re: same field from INNER JOIN tables

ThomasZloch — Mon, 10 Feb 2014 10:37:06 GMT

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

Re: same field from INNER JOIN tables

ThomasZloch — Mon, 10 Feb 2014 10:38:39 GMT

No, there is no performance difference:

Thomas

Re: same field from INNER JOIN tables

Former Member — Tue, 11 Feb 2014 03:07:17 GMT

Hi Susmitha,

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

Re: same field from INNER JOIN tables

Former Member — Tue, 11 Feb 2014 03:19:05 GMT

Hi Maju,

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

Re: same field from INNER JOIN tables

ThomasZloch — Tue, 11 Feb 2014 07:27:28 GMT

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

Re: same field from INNER JOIN tables

Former Member — Tue, 11 Feb 2014 07:36:32 GMT

Try below sample code

select t1~f1

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.

Re: same field from INNER JOIN tables

Former Member — Wed, 12 Feb 2014 04:05:37 GMT

Yes, thanks. It works.