topic Re: Problem in inner join in Application Development and Automation Discussions

Problem in inner join

Former Member — Wed, 26 Sep 2007 13:30:43 GMT

Hi experts,

I am facing problem in joining three tables in the following query. Please let me know where I am doing wrong.

SELECT mara~matnr

mara~mtart

mara~matkl

marc~dispo

marc~werks

mchb~lgort

mchb~charg

mara~mhdhb

INTO CORRESPONDING FIELDS OF TABLE itab

FROM mara

INNER JOIN marc ON mara_{matnr = marc}matnr

INNER JOIN MCHB on ( marc_{matnr = mchb}matnr

and marc_{werks = mchb}werks )

WHERE mara~matnr in s_matnr

and mara~mtart in so_mtart

and mara~matkl in so_matkl

and marc~dispo in so_dispo

and marc~werks in so_werks

and mchb~lgort in so_lgort

and mchb~charg in so_charg.

Re: Problem in inner join

JozsefSzikszai — Wed, 26 Sep 2007 13:50:18 GMT

hi RAJ,

what is wrong? You don't get the result you wanto to? Did you check the values of the select options during runtime?

Re: Problem in inner join

Former Member — Wed, 26 Sep 2007 14:03:31 GMT

I am not getting any values in the internal table even though there is data in all three tables. Yes, there are values in the select-options during runtime.

Re: Problem in inner join

kanthimathikris — Wed, 26 Sep 2007 14:12:45 GMT

Is your itab having field names similar to what you specify as column in select?

Re: Problem in inner join

Former Member — Wed, 26 Sep 2007 14:14:33 GMT

hi try this.............

SELECT mara~matnr

mara~mtart

mara~matkl

marc~dispo

marc~werks

mchb~lgort

mchb~charg

mara~mhdhb

INTO CORRESPONDING FIELDS OF TABLE itab

FROM ( ( mara

INNER JOIN marc ON mara_{matnr = marc}matnr )

INNER JOIN MCHB on marc_{matnr = mchb}matnr

and marc_{werks = mchb}werks )

WHERE mara~matnr in s_matnr

and mara~mtart in so_mtart

and mara~matkl in so_matkl

and marc~dispo in so_dispo

and marc~werks in so_werks

and mchb~lgort in so_lgort

and mchb~charg in so_charg.

Re: Problem in inner join

Former Member — Wed, 26 Sep 2007 14:20:01 GMT

I tried your code but did not work.

Re: Problem in inner join

Former Member — Wed, 26 Sep 2007 14:23:14 GMT

Hi raj

can you paste your entire code so that we can fix the issue

Message was edited by:

varalakshmi kannan

Re: Problem in inner join

Former Member — Wed, 26 Sep 2007 14:32:24 GMT

Hi Raj,

Your code is ok. And seems to be the combinition of the values you input in the selection screen may not have the data.

Try giving materinal number once and try the combition of the values input the selection screen.

Thanks,

Sriram Ponna.

Re: Problem in inner join

Former Member — Wed, 26 Sep 2007 14:32:31 GMT

may the data got filtered through so many conditions....in where clause...

just try to find some test data.........

Re: Problem in inner join

Former Member — Wed, 26 Sep 2007 14:34:37 GMT

Tables : mara

marc

mchb.

data: begin of itab occurs 0,

matnr like mara-matnr, " Material number

mtart like mara-mtart, " Material Type

matkl like mara-matkl, " Material group

dispo like marc-dispo, " MRP Controller

werks like marc-werks, " Plant

lgort like mchb-lgort, " Storage location

charg like mchb-charg, " Batch number

mhdhb like mara-mhdhb, " Total shelf life

end of itab.

selection-screen begin of block selectblk with frame title text-s01.

selection-screen: skip 1.

select-options: so_matnr for mara-matnr, " material

so_mtart for mara-mtart, " Material Type

so_lgort for mchb-lgort, " storage location

so_charg for mchb-charg, " Batch number

so_werks for marc-werks obligatory, " plant

so_matkl for mara-matkl, " material group

so_dispo for marc-dispo, " mrp controller

so_mmstd for marc-mmstd obligatory. " Input date

selection-screen end of block selectblk.

************************************************************************

S T A R T - O F - S E L E C T I O N *

************************************************************************

START-OF-SELECTION.

PERFORM validate_inpt. " Validate input

&----

*& Form validate_inpt

&----

Check validity of select-options

----

SELECT mara~matnr

mara~mtart

mara~matkl

marc~dispo

marc~werks

mchb~lgort

mchb~charg

mara~mhdhb

INTO CORRESPONDING FIELDS OF TABLE itab

FROM ( ( mara

INNER JOIN marc ON mara_{matnr = marc}matnr )

INNER JOIN MCHB on marc_{matnr = mchb}matnr

and marc_{werks = mchb}werks )

WHERE mara~matnr in s_matnr

and mara~mtart in so_mtart

and mara~matkl in so_matkl

and marc~dispo in so_dispo

and marc~werks in so_werks

and mchb~lgort in so_lgort

and mchb~charg in so_charg.

ENDFORM.

Re: Problem in inner join

Former Member — Wed, 26 Sep 2007 14:37:01 GMT

I think your code is OK, so the problem lies elsewhere. You say there is data in all three tables, but is there data that meets the JOIN conditions?

Rob

Re: Problem in inner join

Former Member — Wed, 26 Sep 2007 14:39:12 GMT

Hi,

Change the following:

SELECT mara~matnr

mara~mtart

mara~matkl

mara~mhdhb to be here

marc~dispo

marc~werks

mchb~lgort

mchb~charg

mara~mhdhb not to be here

INTO CORRESPONDING FIELDS OF TABLE itab

FROM ( ( mara

INNER JOIN marc ON mara_{matnr = marc}matnr )

INNER JOIN MCHB on marc_{matnr = mchb}matnr

and marc_{werks = mchb}werks )

WHERE mara~matnr in s_matnr

and mara~mtart in so_mtart

and mara~matkl in so_matkl

and marc~dispo in so_dispo

and marc~werks in so_werks

and mchb~lgort in so_lgort

and mchb~charg in so_charg.

Regards

Kannaiah

Re: Problem in inner join

Former Member — Wed, 26 Sep 2007 14:44:45 GMT

Hi,

I tried your change but don't know why it still does not work.

Thanks,

Raj

Re: Problem in inner join

Former Member — Wed, 26 Sep 2007 14:47:11 GMT

Hi,

Try commenting all the conditions mentioned in the select statement where condition and consider only matnr. If it is working you need to uncomment one by one in where condition and test it. If you get data considering only MATNR in where condition then there is no problem in your select statement.

Thanks,

Sriram Ponna.

Message was edited by:

Sriram Ponna

Re: Problem in inner join

Former Member — Wed, 26 Sep 2007 14:53:15 GMT

Hi ,

try this:

SELECT mara~matnr

mara~mtart

mara~matkl

marc~dispo

marc~werks

mchb~lgort

mchb~charg

INTO CORRESPONDING FIELDS OF TABLE t_data

FROM mara as mara

inner join marc as marc on mara_{matnr = marc}matnr

inner join mchb as mchb on marc_{matnr = mchb}matnr

WHERE mara~matnr in s_matnr

AND mara~mtart in s_mtart

AND mara~matkl in s_matkl

AND marc~dispo in s_dispo

AND marc~werks in s_werks

AND mchb~lgort in s_lgort

AND mchb~charg in s_charg

Regards

Kannaiah

Re: Problem in inner join

Former Member — Wed, 26 Sep 2007 14:53:32 GMT

hi your coding is perfectly alright

i suspect the problem will be with your inputs.

Re: Problem in inner join

Former Member — Wed, 26 Sep 2007 14:55:22 GMT

You can try this too...........

SELECT mara~matnr

mara~mtart

mara~matkl

marc~dispo

marc~werks

mchb~lgort

mchb~charg

mara~mhdhb

INTO CORRESPONDING FIELDS OF TABLE itab

FROM ( ( mara

INNER JOIN marc ON marc_{matnr = mara}matnr )

INNER JOIN MCHB on mchb_{matnr = marc}matnr

and mchb_{werks = marc}werks )

WHERE mara~matnr in s_matnr

and mara~mtart in so_mtart

and mara~matkl in so_matkl

and marc~dispo in so_dispo

and marc~werks in so_werks

and mchb~lgort in so_lgort

and mchb~charg in so_charg.

I think this should work!

Regards,

Priya

Re: Problem in inner join

Former Member — Wed, 26 Sep 2007 14:56:27 GMT

HI,

TRY THIS:

SELECT A~matnr

A~mtart

A~matkl

A~mhdhb

B~dispo

B~werks

C~lgort

C~charg

INTO CORRESPONDING FIELDS OF TABLE itab

FROM mara AS A

INNER JOIN marc AS B ON A_{matnr = B}matnr

INNER JOIN MCHB AS C on A_{matnr = C}matnr

and B_{werks = C}werks

WHERE A~matnr in s_matnr

and A~mtart in so_mtart

and A~matkl in so_matkl

and B~dispo in so_dispo

and B~werks in so_werks

and C~lgort in so_lgort

and C~charg in so_charg.

Re: Problem in inner join

Former Member — Wed, 26 Sep 2007 14:57:37 GMT

i tried with this data. in ur prgm..it worked fine ....itab got populated with 2 records....just try it ......

matnr

000000000000011810

000000000000011820

000000000000011890

000000000000011899

werks -1000

sy-datum

Re: Problem in inner join

Former Member — Wed, 26 Sep 2007 15:06:42 GMT

Maybe you should try an OUTER JOIN on MCHB:

SELECT mara~matnr mara~mtart mara~matkl marc~dispo
       marc~werks mchb~lgort mchb~charg mara~mhdhb
  INTO CORRESPONDING FIELDS OF TABLE itab
  FROM mara
  INNER JOIN marc  ON   mara~matnr = marc~matnr
  LEFT OUTER JOIN mchb  ON ( marc~matnr = mchb~matnr
                  AND   marc~werks = mchb~werks ).
where mara~matnr in s_matnr
  and mara~mtart in so_mtart
  and mara~matkl in so_matkl
  and marc~dispo in so_dispo
  and marc~werks in so_werks
  and mchb~lgort in so_lgort
  and mchb~charg in so_charg.

Rob