Problem in inner join

[Former Member]

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.

[JozsefSzikszai]

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?

ec

[Former Member]

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.

[kanthimathikris]

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

[Former Member]

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.

[Former Member]

I tried your code but did not work.

[Former Member]

Hi raj

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

Message was edited by:

varalakshmi kannan

[Former Member]

hi

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

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

[Former Member]

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.

[Former Member]

Hi,

Change the following:

SELECT mara~matnr

mara~mtart

mara~matkl

<b>mara~mhdhb</b> to be here

marc~dispo

marc~werks

mchb~lgort

mchb~charg

<b>mara~mhdhb</b> <b>not to be here </b>

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

[Former Member]

Hi,

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

Thanks,

Raj

[Former Member]

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

[Former Member]

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

[Former Member]

hi your coding is perfectly alright

i suspect the problem will be with your inputs.

[Former Member]

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

[Former Member]

hi

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

[Former Member]

Hi Sriram,

Your suggestion really helped. It's just not taking that marc~dispo condition . But I still need to check the dispo input value. Can this be made to work?

Thanks,

Raj

[Former Member]

Hi,

By checking the where conditions commenting and uncommenting you can narrow down to the problem.

Thanks,

Sriram Ponna.

[Former Member]

Thanks to all. My issue is resolved. I hav awarded points for helpful answers.

[Former Member]

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.

[Former Member]

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

[Former Member]

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.

[Former Member]

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