topic Re: Problem when doing inner join for three tables.... in Application Development and Automation Discussions

Problem when doing inner join for three tables....

Former Member — Thu, 06 Jul 2006 21:03:42 GMT

DATA: V3 TYPE VBAP-MAKTL.

SELECT single vbap~matkl into V3

from VBAP

inner join VBFA

inner join LIPS

on LIPS_{POSNR = VBFA}POSNN

and vbap_{vbeln = vbfa}vbelv

and vbap_{posnr = vbfa}posnv

where LIPS_{VBELN = VBFA}VBELN = '0080000834'

and vbfa~vbtyp_n = 'J'

and LIPS~J_3ASIZE = '0884'.

Error: Wrong expression "INNER" in from clause...

Re: Problem when doing inner join for three tables....

Former Member — Thu, 06 Jul 2006 21:06:02 GMT

Tushar,

Do this..

SELECT single vbap~matkl into V3

from VBAP

inner join VBFA

on vbap_{vbeln = vbfa}vbelv

vbap_{posnr = vbfa}posnv

inner join LIPS

on LIPS_{POSNR = VBFA}POSNN

where LIPS_{VBELN = VBFA}VBELN = '0080000834'

and vbfa~vbtyp_n = 'J'

and LIPS~J_3ASIZE = '0884'.

Message was edited by: Phani Kiran Nudurupati

Re: Problem when doing inner join for three tables....

Former Member — Thu, 06 Jul 2006 21:06:32 GMT

hi Tushar,

Your syntax for inner join is wrong refer help

Re: Problem when doing inner join for three tables....

Former Member — Thu, 06 Jul 2006 21:08:46 GMT

SELECT single vbap~matkl into V3

from ( ( VBAP inner join VBFA

ON vbap-vbeln = vbfa-vbelp )

inner join LIPS

on LIPS_{POSNR = VBFA}POSNN

and vbap_{vbeln = vbfa}vbelv

and vbap_{posnr = vbfa}posnv )

where LIPS_{VBELN = VBFA}VBELN = '0080000834'

and vbfa~vbtyp_n = 'J'

and LIPS~J_3ASIZE = '0884'.

Re: Problem when doing inner join for three tables....

Former Member — Thu, 06 Jul 2006 21:10:00 GMT

SELECT single vbap~matkl into V3

from ( ( VBAP as vbap inner join VBFA as vbfa

ON vbap-vbeln = vbfa-vbelp )

inner join LIPS as lips

on LIPS_{POSNR = VBFA}POSNN

and vbap_{vbeln = vbfa}vbelv

and vbap_{posnr = vbfa}posnv )

where LIPS_{VBELN = VBFA}VBELN = '0080000834'

and vbfa~vbtyp_n = 'J'

and LIPS~J_3ASIZE = '0884'.

Re: Problem when doing inner join for three tables....

Former Member — Thu, 06 Jul 2006 21:10:31 GMT

Hello Tushar,

Why you are using the 3 table inner join. If the volume of data is big then this statement will give to time out. Use "for all entries" statement to select the data from the table.

select field1 field2 from LIPS into i_lips

where LIPS~J_3ASIZE = '0884'.

select f3 f4 from vbfa into i_vbfa for all entries in i_lips where field1 = i_lips-field1 and so on

and

use same to select data from VBAP.

Hope this will help

Re: Problem when doing inner join for three tables....

Former Member — Thu, 06 Jul 2006 21:12:35 GMT

SELECT single vbap~matkl into V3

from VBAP

inner join VBFA

on vbap_{vbeln = vbfa}vbelv

and vbap_{posnr = vbfa}posnv

inner join LIPS

on LIPS_{POSNR = VBFA}POSNN

and LIPS_{VBELN = VBFA}VBELN

where LIPS~VBELN = '0080000834'

and vbfa~vbtyp_n = 'J'

and LIPS~J_3ASIZE = '0884'.