Inner Joi Issue

[Former Member]

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.

[Former Member]

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.

[Former Member]

hi:

please check is there data in vbak,vbap,vbkd which accord with the condition

"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.".

SELECT...

...

FROM <tab> [INNER] JOIN <dbtab> [AS <alias>] ON <cond> <options>

...

If <dbtab> does not contain any lines that meet the condition <cond>, the line from <tab> is not included in the selection.

SELECT...

...

FROM <tab> LEFT [OUTER] JOIN <dbtab> [AS <alias>] ON <cond>

<options>

...

If <dbtab> does not contain any lines that meet the condition <cond>, the system includes a single line in the selection whose columns from <dbtab> are filled with null values.

In the left outer join, more restrictions apply to the condition <cond> than in the inner join. In addition to the above restrictions:

EQ or = is the only permitted relational operator.

There must be at least one comparison between columns from <tab> and <dbtab>.

The WHERE clause may not contain any comparisons with columns from <dbtab>. All comparisons using columns from <dbtab> must appear in the condition <cond>.

(refered in "sap library abap programming. Specifying Database Tables ")

Regards

[Former Member]

Hello Sridhar,

I have checked ur program :

REPORT zxyz.

tables : vbak,

vbap,

vbkd.

DATA : BEGIN OF i_data OCCURS 0,

vbeln LIKE vbak-vbeln,

posnr LIKE vbap-posnr,

route LIKE vbap-route,

bstkd_e LIKE vbkd-bstkd_e,

posex-e LIKE vbkd-posex_e,

END OF i_data.

SELECT-OPTIONS: s_erdat FOR vbak-erdat, "SO CREATION DATE

s_vbeln FOR vbak-vbeln, " SALES ORDER

s_bstkd FOR vbkd-bstkd_e. " CLIENT PURCHASE ORDER NO

START-OF-SELECTION.

SELECT a_{vbeln b}posnr b_{route c}bstkd_e c~posex_e

INTO TABLE i_data

FROM vbak as a

INNER JOIN vbap AS b

ON a_{vbeln = b}vbeln

INNER JOIN vbkd as c

ON a_{vbeln = c}vbeln

AND b_{posnr = c}posnr

WHERE a~erdat IN s_erdat

AND a~vbeln IN s_vbeln

AND c~bstkd_e IN s_bstkd.

loop at i_data.

write:/ i_data-vbeln,i_data-posnr,i_data-bstkd_e.

endloop.

I think problem might be vbkd-posnr,you are comparing with vbap-posnr = vbkd-posnr,so data is not available.

first check the data in vbak,vbap,vbkd,if the record is available then what i written the code should work.

Thanks

Seshu