Join conditions

[Former Member]

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.

[Former Member]

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.

[Former Member]

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

[Former Member]

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...

???

[christine_evans]

>

> 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.

[Former Member]

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!

[Former Member]

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

[ThomasZloch]

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