Problem in left inner join on 3 tables

[Former Member]

Hi All

Can anyone help me for the below issue

While i am executing the below select queary i am getting the error "No fields from the right-hand table of a LEFT OUTER JOIN may appear in the WHERE condition: "YMM1057~FILMID".

SELECT vbap~vbeln

vbap~posnr

vbap~matnr

vbap~arktx

vbap~kdmat

vbap~werks

vbap~aedat

vbap~j_3acada

vbap~j_3acqlf

vbap~j_3arqda

vbap~j_3arqlf

vbap~zzj_3adepm

vbap~zzprod_pgm

vbap~zzsbu

ymm1057~filmid

ysdctm_film~zzreference_nbr

INTO TABLE l_it_vbap

FROM ( ( vbap

LEFT OUTER JOIN ymm1057 ON

vbap_{vbeln EQ ymm1057}vbeln AND

vbap_{posnr EQ ymm1057}posnr )

INNER JOIN ysdctm_film ON

ysdctm_film_{zzfilm_id EQ ymm1057}filmid )

FOR ALL ENTRIES IN it_vbak

WHERE vbap~vbeln EQ it_vbak-vbeln

AND vbap~matnr IN s_matnr

AND vbap~werks IN s_werks

AND vbap~j_3acada IN s_cancel

AND vbap~j_3arqda IN s_rdd

AND vbap~zzsbu IN s_sbu

AND vbap~kdmat IN s_cmat

AND ymm1057~filmid IN s_film

AND ysdctm_film~zzreference_nbr IN s_refid.

Thanks much

Sowmya

[seshatalpasai_madala]

Hi,

Use

SELECT vbap~vbeln

vbap~posnr

vbap~matnr

vbap~arktx

vbap~kdmat

vbap~werks

vbap~aedat

vbap~j_3acada

vbap~j_3acqlf

vbap~j_3arqda

vbap~j_3arqlf

vbap~zzj_3adepm

vbap~zzprod_pgm

vbap~zzsbu

ymm1057~filmid

ysdctm_film~zzreference_nbr

INTO TABLE l_it_vbap

FROM ( ( vbap

LEFT OUTER JOIN ymm1057 ON

vbap_{vbeln EQ ymm1057}vbeln AND

vbap_{posnr EQ ymm1057}posnr )

INNER JOIN ysdctm_film ON

ysdctm_film_{zzfilm_id EQ ymm1057}filmid )

FOR ALL ENTRIES IN it_vbak

WHERE vbap~vbeln EQ it_vbak-vbeln

AND vbap~matnr IN s_matnr

AND vbap~werks IN s_werks

AND vbap~j_3acada IN s_cancel

AND vbap~j_3arqda IN s_rdd

AND vbap~zzsbu IN s_sbu

AND vbap~kdmat IN s_cmat

AND ysdctm_film~zzfilm_id IN s_film

AND ysdctm_film~zzreference_nbr IN s_refid.

Since you are anyway doing an inner join on film id between ysdctm_film and ymm1057 you can use ysdctm_film~zzfilm_id in the where clause.

Regards,

Sesh

[former_member585060]

Hi,

Try below way

SELECT vbap~vbeln
vbap~posnr
vbap~matnr
vbap~arktx
vbap~kdmat
vbap~werks
vbap~aedat
vbap~j_3acada
vbap~j_3acqlf
vbap~j_3arqda
vbap~j_3arqlf
vbap~zzj_3adepm
vbap~zzprod_pgm
vbap~zzsbu
ymm1057~filmid
ysdctm_film~zzreference_nbr
INTO TABLE l_it_vbap
FROM ( ( ymm1057                                                    " Use ymm1057 first
LEFT OUTER JOIN vbap ON                                        "Change as here in ur select statement
ymm1057~vbeln EQ vbap~vbeln  AND
ymm1057~posnr EQ vbap~posnr  )                               "
INNER JOIN ysdctm_film ON                                       "
ymm1057~filmid EQ ysdctm_film~zzfilm_id )                 "
FOR ALL ENTRIES IN it_vbak
WHERE vbap~vbeln EQ it_vbak-vbeln
AND vbap~matnr IN s_matnr
AND vbap~werks IN s_werks
AND vbap~j_3acada IN s_cancel
AND vbap~j_3arqda IN s_rdd
AND vbap~zzsbu IN s_sbu
AND vbap~kdmat IN s_cmat
AND ymm1057~filmid IN s_film
AND ysdctm_film~zzreference_nbr IN s_refid.

Regards

Bala Krishna

[Former Member]

hi.

simple is that the fields of tables which u are using in inner join , write those fields in where condition.

just like

select * from ztable
inner join ztable2 on ( key fields)
where ztable2~field = ?
and ztable~field =?

.