topic Re: Join Table in Application Development and Automation Discussions

Join Table

Former Member — Wed, 28 Dec 2011 09:59:28 GMT

Hi,

I read the following doc about abap joins.

http://help.sap.com/abapdocu_70/en/ABAPSELECT_JOIN.htm

and have coded something like this:

SELECT *
  INTO  CORRESPONDING FIELDS OF TABLE itab
  FROM  ( ( ( text AS p
            INNER JOIN book AS f ON p~book = f~book )

            INNER JOIN rep AS c ON c~id = f~id )
            INNER JOIN elt AS t ON t~tu = c~co )

WHERE p~vers = '1' AND
      f~vers = '1' AND
      c~vers = '1' AND
      t~vers = '1' AND
      p~langu   IN langu_s AND
      t~langu   IN langu_s.

Now I would like to have all entries from rep !!!

If I make a Left (outer) Join I have to remove the where clause, but i just want to get vers 1 from rep.

How else can I code it? The best thing would be to get all entries from rep and all from book, and join them if possible...

but ABAP Doc is talking only about inner and left outer join.

Thanks

Moderator Message: Please use a more meaningful subject line.

Edited by: Suhas Saha on Dec 29, 2011 2:29 PM

Re: Join Table

madhu_vadlamani — Wed, 28 Dec 2011 10:06:02 GMT

Hi Alto,

but ABAP Doc is talking only about inner and left outer join

Can you explain it.Yes you can check with for all entries.if it is more than three tables for all entries is better than join.

Regards,

Madhu.

Re: Join Table

Former Member — Wed, 28 Dec 2011 10:06:12 GMT

HI Alto,

You can mention c~vers = '1' in ON condition of Join itself. You don't have to mention it in where condition.

Regards,

Selva M

Re: Join Table

mayankmrai — Wed, 28 Dec 2011 10:18:48 GMT

hope you have got the solution to the issue u are facing...

but see to it that to increase the performance of your query avoid using 'corresponding fields of' and 'join conditions' better make use of 'for all entries in'..

Re: Join Table

nabheetscn — Wed, 28 Dec 2011 10:29:58 GMT

Fromthe discussion which i have a read and personal experince inner join most of the time performs better than for all entires. Please add a condtion for version thats it.

Nabheet

Re: Join Table

Former Member — Wed, 28 Dec 2011 10:35:55 GMT

Yes Nabheet, but if you use more than 3 tables which have huge data then you need to specify as many number of key fields as you can in the ON and WHERE conditions to avoid performance issue or to run faster than FOR ALL ENTRIES.

Regards,

Selva M

Re: Join Table

former_member777515 — Thu, 29 Dec 2011 02:54:16 GMT

JOIN statement bypass SAP buffering and access database table more faster. Of course, you can use BYPASSING BUFFER in FOR ALL ENTRIES too...

depends on the requirement and just adjust accordingly.

Re: Join Table

Former Member — Thu, 29 Dec 2011 08:14:35 GMT

Hi again,

I am looking for a tool or transaction in SAP that will automatically generate select (join) statements.

I mean it should be possible to say how to join this an that with a particular condition and

i will get a clear select statement, and I only have to adjust it a bit...

Does something like that exists?

Thanks

Re: Join Table

Former Member — Thu, 29 Dec 2011 08:55:41 GMT

Hi ,

You can not get directly select statement with all the condition with any of the tools provided by SAP. But you can find the join conditions between the tables using SQ01 or SQVI

1. create Quick view ---> Select datasource as Table Join --->Ok

2.Go to edit-->insert tables

then It'll display the layout with join conditions.

Regards,

Selva M