JOIN operation on Three tables?

[shilpik20]

I need to validate the input field vbak~bstnk field in the Select query below:-

SELECT vbrk_{vbeln vbrp}posnr uepos

fkimg kostl vrkme matnr pstyv kunag

vkorg vtweg knumv fkdat vbrk~erdat kunrg

INTO table it_inv

FROM vbrk

JOIN vbrp on

vbrk_{vbeln = vbrp}vbeln for all entries in it_bkpf

WHERE vbrk~vbeln = it_bkpf-awkey and

vbrk~vbeln in s_vbeln and

fktyp = 'A' and

vbtyp = 'M' and

vbtyp = 'O' and

vkorg in s_vkorg and

vtweg in s_vtweg and

fkdat in s_bdate and

kunrg in s_kunrg and

kunag in s_kunnr and

kostl in s_kostl and

vbrk~spart in s_spart and

vstel in s_vstel and

werks in s_werks.

I need to add s_bstnk (corresponding input field for vbak-bstnk) in the above select query , in order to validate it. How should i restructure the SELECT query to include the third table VBAK.

[shilpik20]

I need to validate the input field vbak~bstnk field in the Select query below:-

SELECT vbrk_{vbeln vbrp}posnr uepos

fkimg kostl vrkme matnr pstyv kunag

vkorg vtweg knumv fkdat vbrk~erdat kunrg

INTO table it_inv

FROM vbrk

JOIN vbrp on

vbrk_{vbeln = vbrp}vbeln for all entries in it_bkpf

WHERE vbrk~vbeln = it_bkpf-awkey and

vbrk~vbeln in s_vbeln and

fktyp = 'A' and

vbtyp = 'M' and

vbtyp = 'O' and

vkorg in s_vkorg and

vtweg in s_vtweg and

fkdat in s_bdate and

kunrg in s_kunrg and

kunag in s_kunnr and

kostl in s_kostl and

vbrk~spart in s_spart and

vstel in s_vstel and

werks in s_werks.

I need to add s_bstnk (corresponding input field for vbak-bstnk) in the above select query , in order to validate it. How should i restructure the SELECT query to include the third table VBAK.

[Former Member]

Hi,

chek this..

u need to give field like vbrk~fld to all fields u r selecting... and also in where condition... i am not sure which table consists those fields so i didn't mention

SELECT vbrk_{vbeln vbrp}posnr uepos

fkimg kostl vrkme matnr pstyv kunag

vkorg vtweg knumv fkdat vbrk~erdat kunrg

INTO table it_inv

FROM vbrk

I<b>NNERJOIN vbrp on

vbrk_{vbeln = vbrp}vbeln INNERJOIN VBAK ON VBRK_{VBELN = VBAK}VBELN </b>for all entries in it_bkpf

WHERE vbrk~vbeln = it_bkpf-awkey and

vbrk~vbeln in s_vbeln and

fktyp = 'A' and

vbtyp = 'M' and

vbtyp = 'O' and

vkorg in s_vkorg and

vtweg in s_vtweg and

fkdat in s_bdate and

kunrg in s_kunrg and

kunag in s_kunnr and

kostl in s_kostl and

vbrk~spart in s_spart and

vstel in s_vstel and

werks in s_werks and

<b>vbak~bstnk IN s_bstnk.</b>

Regards

SAB

[Former Member]

Hi,

Try this..Changes marked in bold..

SELECT vbrk_{vbeln vbrp}posnr uepos

fkimg kostl vrkme matnr pstyv kunag

vkorg vtweg knumv fkdat vbrk~erdat kunrg

INTO table it_inv

FROM vbrk

JOIN vbrp on

vbrk_{vbeln = vbrp}vbeln

<b>INNER JOIN vbak</b>

<b>ON vbrp_{aubel = vbak}vbeln</b>

for all entries in it_bkpf

WHERE vbrk~vbeln = it_bkpf-awkey and

vbrk~vbeln in s_vbeln and

fktyp = 'A' and

vbtyp = 'M' and

vbtyp = 'O' and

vkorg in s_vkorg and

vtweg in s_vtweg and

fkdat in s_bdate and

kunrg in s_kunrg and

kunag in s_kunnr and

kostl in s_kostl and

vbrk~spart in s_spart and

vstel in s_vstel and

werks in s_werks

<b>AND vbak~bstnk IN s_bstnk</b>.

Thanks,

Naren

[Former Member]

Hi

SELECT a_{vbeln b}posnr b~uepos

b_{fkimg b}kostl b_{vrkme b}matnr b~pstyv kunag

a_{vkorg a}vtweg a_{knumv a}fkdat a_{erdat a}kunrg

INTO table it_inv

FROM vbrk as a

JOIN vbrp as b on

a_{vbeln = b}vbeln join vbak as c

on b_{aubel = c}vbeln

for all entries in it_bkpf

WHERE a~vbeln = it_bkpf-awkey and

a~vbeln in s_vbeln and

a~fktyp = 'A' and

a~vbtyp = 'M' and

a~vbtyp = 'O' and

a~vkorg in s_vkorg and

a~vtweg in s_vtweg and

a~fkdat in s_bdate and

a~kunrg in s_kunrg and

a~kunag in s_kunnr and

b~kostl in s_kostl and

a~spart in s_spart and

b~vstel in s_vstel and

b~werks in s_werks and

c~bstnk in s_bstnk.

Reward points if useful

Regards

Anji