topic join between tables in Application Development and Automation Discussions

join between tables

Former Member — Tue, 15 Nov 2005 11:46:39 GMT

what is wrong :

SELECT viser02~sdaufnr

FROM (viser02 INNER JOIN vbak

ON vbak_{vbeln = viser02}sdaufnr

AND vbak_{posnr = viser02}posnr

INNER JOIN vbap

ON vbap_{vbeln = viser02}sdaufnr

AND vbap_{posnr = viser02}posnr)

where vbak~auart = 'WV'

and ( vbap~sobkz <> 'V'

OR vbap~sobkz <> 'W' ).

Re: join between tables

athavanraja — Tue, 15 Nov 2005 11:52:29 GMT

SELECT viser02~sdaufnr

FROM <b>(</b>viser02 INNER JOIN vbak

ON vbak_{vbeln = viser02}sdaufnr

AND vbak_{posnr = viser02}posnr

INNER JOIN vbap

ON vbap_{vbeln = viser02}sdaufnr

AND vbap_{posnr = viser02}posnr<b>)</b>

where vbak~auart = 'WV'

and ( vbap~sobkz <> 'V'

OR vbap~sobkz <> 'W' ).

remove the highlighted brackets.

SELECT viser02~sdaufnr

FROM viser02 INNER JOIN vbak

ON vbak_{vbeln = viser02}sdaufnr

AND vbak_{posnr = viser02}posnr

INNER JOIN vbap

ON vbap_{vbeln = viser02}sdaufnr

AND vbap_{posnr = viser02}posnr

where vbak~auart = 'WV'

and ( vbap~sobkz <> 'V'

OR vbap~sobkz <> 'W' ).

Regards

Raja

Re: join between tables

Former Member — Tue, 15 Nov 2005 11:56:30 GMT

Yehiel,

Change the Code to the following:

SELECT viser02~sdaufnr

FROM ((viser02

INNER JOIN vbak

ON vbak_{vbeln = viser02}sdaufnr

AND vbap_{posnr = viser02}posnr)

INNER JOIN vbap

ON vbap_{vbeln = viser02}sdaufnr

AND vbap_{posnr = viser02}posnr)

where vbak~auart = 'WV'

and (vbap~sobkz <> 'V'

OR vbap~sobkz <> 'W') .

Thanks

Kam

Re: join between tables

Former Member — Tue, 15 Nov 2005 15:53:50 GMT

Hi use the following code.

select viser02~sdaufnr

into table itab from ( ( viser02 as viser02 inner join vbak as vbak on vbak_{vbeln = viser02}sdaufnr and vbak_{posnr = viser02}posnr )

( inner join vbap as vbap on vbap_{vbeln = viser02}sdaufnr AND vbap_{posnr = viser02}posnr ) where vbak_{auart = 'WV' and vbap}sobkz <> 'V' OR vbap~sobkz <> 'W' ).

Re: join between tables

Former Member — Tue, 15 Nov 2005 16:31:06 GMT

Hi,

Write the join as follows

DATA: BEGIN OF ITAB_AUFNR OCCURS 0,

SDAUFNR LIKE VISER02-SDAUFNR,

END OF ITAB_AUFNR.

SELECT viser02~sdaufnr

INTO TABLE ITAB_AUFNR

FROM viser02

INNER JOIN vbak

ON vbak_{vbeln = viser02}sdaufnr

INNER JOIN vbap

ON vbap_{vbeln = viser02}sdaufnr

AND vbap_{posnr = viser02}posnr

where vbak~auart = 'WV'

and ( vbap_{sobkz <> 'V' OR vbap}sobkz <> 'W' ).

The problem with your join is in VBAK there will not be any item field(POSNR) and you also didn't specify the into clause.

As a best practice do not use negative conditions in where cluases, they will consume more time. There will be two options to eliminate this.

1. Populate ranges table with all the allowed values for the SOBKZ field and use it in where clause.

2. Populate ranges table with the not required values with the sign option 'E' (Exclude).

If you need any further help let me know.

Thanks

Giridhar