topic Re: Join conditions in Application Development and Automation Discussions

Join conditions

Former Member — Mon, 27 Apr 2009 10:07:50 GMT

Hi all,

I need to JOIN few tables. I will post you my code example and I need any info on corrections of this JOIN conditions because I get too many records in output. Also columns of data that is supposed to be fetched from VBAK table are empty.Here it is:

SELECT *

FROM vbak AS ak INNER JOIN vbfa AS fa

ON ak_{vbeln = fa}vbelv

INNER JOIN vbrk AS rk

ON fa_{vbeln = rk}vbeln

INNER JOIN kna1 AS a1

ON rk_{kunrg = a1}kunnr

INTO CORRESPONDING FIELDS OF TABLE it_report

WHERE fa~vbeln IN s_vbeln

AND rk~fkart IN s_fkart

AND rk~fkdat IN s_fkdat

AND rk~vkorg EQ 'HTSD'

AND rk~vtweg IN s_vtweg

AND rk~kunrg IN s_kunrg

AND rk~kunag IN s_kunag

AND a1~stcd1 IN s_stcd1

AND rk~erdat IN s_erdat

AND ak~vkbur IN s_vkbur

AND ak~augru IN s_augru.

Re: Join conditions

Former Member — Mon, 27 Apr 2009 10:11:18 GMT

Hello,

It seems one of the select-options ranges in the where clause contains initial values due to which all entries from the table are getting fetched.

If the range is initial i.e. empty then too many records will be fetched.

To avoid this in case the range is empty don't pass it in where clause.

Regards,

Mansi.

Edited by: SAP USER on Apr 27, 2009 12:11 PM

Re: Join conditions

Former Member — Mon, 27 Apr 2009 10:13:20 GMT

but still...why can't I get any data from VBAK table? all the fields that are fetched from this table are left empty.... So I think that JOIN is not good...

???

Re: Join conditions

Former Member — Mon, 27 Apr 2009 10:13:57 GMT

You need to check the initiality of all the selct-optons you are using.

other wise you will all the records which is initial.

Thanks!

Re: Join conditions

Former Member — Mon, 27 Apr 2009 11:27:45 GMT

Hi Maja,

Dont use joins with somany system tables awith bulk data, it will reduce u perormance .

use like this.

select fields from table1 into itab1 where condition.

if not itab1 is initial.

select fields rom table2 into itab2 whee condition from itab1.

.................

and so on.

Regards

Re: Join conditions

christine_evans — Mon, 27 Apr 2009 11:52:41 GMT


> but still...why can't I get any data from VBAK table? all the fields that are fetched from this table are left empty.... So I think that JOIN is not good...
> 
> ???

If the query is bringing back data, then the join is working. If it's bringing back too may records then your joins may not be specific enough. If your VBAK fields are not being filled and you're using SELECT * and INTO CORRESPONDING FIELDS, then I would guess that you receiving itab does not contain any fields with the same names as the VBAK fields.

Re: Join conditions

ThomasZloch — Mon, 27 Apr 2009 12:10:32 GMT

Please post the structure of it_report here. Either field names don't match or the same field name appears in multiple tables with different values (<empty> being one of them).

Reg. too many records -> that's probably due to table VBFA being involved, which stores document flow on item basis, whereas all your desired information and selection criteria seems to be on header level. You should investigate in that direction.

Thomas