topic Inner join issue in Application Development and Automation Discussions

Inner join issue

Former Member — Sun, 08 Jul 2007 06:52:30 GMT

Hi Gurus,

iam using an inner joiN ON vbak,vabp,vbkd.

the selection screen is

select-options: s_erdat for vbak-erdat, "SO CREATION DATE

s_vbeln for g_vbeln, " SALES ORDER

s_bstkd for vbkd-bstkd_e. " CLIENT PURCHASE ORDER NO

THE sample records:

SO soline PO poline

86333 1 - -

87000 1 49000 1

my inner join:

select vbak_{vbeln vbap}posnr vbap_{route vbkd}bstkd_e vbkd~posex_e

into table t_vbap

from vbak

inner join vbap

on vbak_{vbeln = vbap}vbeln

inner join vbkd

on vbap_{vbeln = vbkd}vbeln

and vbap_{posnr = vbkd}posnr

where vbak~erdat in s_erdat

and vbap~vbeln in s_vbeln

and vbkd~bstkd_e in s_bstkd.

problem:

selection screen:

DATE: - TO -

SO : 86333 TO -

PO : - TO -

INPUT IS ONLY sales order no.

and in V BKD NO po - blank .

In VBKD table if the PO is blank am not getting any record.

mean the first one should be retrived but not happening.

at least it should show me like this below.

86333 1 - -

pls clera me asap.

Re: Inner join issue

Former Member — Sun, 08 Jul 2007 07:13:19 GMT

Hi,

use left outer join for VBKD

select vbak_{vbeln vbap}posnr vbap_{route vbkd}bstkd_e vbkd~posex_e

into table t_vbap

from vbak

inner join vbap

on vbak_{vbeln = vbap}vbeln

LEFT OUTER join vbkd

on vbap_{vbeln = vbkd}vbeln

and vbap_{posnr = vbkd}posnr

where vbak~erdat in s_erdat

and vbap~vbeln in s_vbeln

and vbkd~bstkd_e in s_bstkd.

Eg.

SELECT s_{carrid s}carrname p~connid

INTO CORRESPONDING FIELDS OF TABLE itab

FROM scarr AS s

LEFT OUTER JOIN spfli AS p ON s_{carrid = p}carrid

AND p~cityfrom = p_cityfr.

Regards

Rajiv singh.

Re: Inner join issue

Former Member — Sun, 08 Jul 2007 07:28:47 GMT

Rajjiv ,

i changed according ly but its giving the below error.

NO-FILEDS FROM TEH RIGHT-HAND SIDE OF TABLE OF left -outer join may appear in WHERE condition : " vbkd~bstkd_e.

it seems in where conditon its not allowing this.