topic Inner join in Application Development and Automation Discussions

Inner join

Former Member — Sun, 13 Jan 2008 18:46:51 GMT

Hi Guys I am trying to learn Inner join and I have written the following code...can you please tell me what I am doing wrong:

REPORT ZMM_DETAIL.

data: begin of it_mara occurs 0,

matnr like mara-matnr,

matkl like mara-matkl,

mtart like mara-mtart,

end of it_mara.

data: begin of it_marc occurs 0,

matnr like marc-matnr,

werks like marc-werks,

ekgrp like marc-ekgrp,

end of it_marc.

select mara-matnr mara-matkl mara-mtart marc-werks marc-ekgrp

into table it_mara

from mara

inner join marc on marc-matnr eq mara-matnr and mara-mtart eq 'HALB'.

what I am trying to achieve is list the plant number, plannng grup foe all those materials where mtart =

HALB

Thanks,

Rajeev

Re: Inner join

DirkAltmann — Sun, 13 Jan 2008 18:57:12 GMT

Hi Rajeev,

try this code.

REPORT ZMM_DETAIL.

data: begin of it_mara occurs 0,

matnr like mara-matnr,

matkl like mara-matkl,

mtart like mara-mtart,

werks like marc-werks,

ekgrp like marc-ekgrp,

end of it_mara.

select mara~matnr

mara~matkl

mara~mtart

marc~werks

marc~ekgrp

into table it_mara

from mara

inner join marc

on marc_{matnr eq mara}matnr

where mara~mtart eq 'HALB'.

Regards,

Dirk

Re: Inner join

Former Member — Sun, 13 Jan 2008 20:27:21 GMT

Hi rajeev,

The no of rows in internal table it_mara that u defined are of total 3.

where as the total no of rows u are retrieving from the database are 5

and u are appending these 5 fields into 3 field structure which is not possible.

hope u got the bug.

regards.

shashikanth naram.

Re: Inner join

Former Member — Sun, 13 Jan 2008 21:18:00 GMT

hai

while writing inner join u may concentrate on the fields where u want u have actual join. u may not get data which isnt matching the condition or may not present in the database... just u specific in the field selection of the joining tables... ok

regards

Ram.

Re: Inner join

Former Member — Mon, 14 Jan 2008 04:50:13 GMT

hi rajeev gupta,

Before using inner join you must declare a internal table

having all the fields you want to retrieve,then fetch the

values into that internal table using inner join using

the primary key and the condition you require.

in your scenario

REPORT ZMM_DETAIL.

data: begin of it_mara occurs 0,

matnr like mara-matnr,

matkl like mara-matkl,

mtart like mara-mtart,

end of it_mara.

data: begin of it_marc occurs 0,

matnr like marc-matnr,

werks like marc-werks,

ekgrp like marc-ekgrp,

end of it_marc.

select mara-matnr mara-matkl mara-mtart marc-werks marc-ekgrp

into table it_mara

from mara

inner join marc on marc-matnr eq mara-matnr and mara-mtart eq 'HALB'.

the select query you have written is right but you are fetching into an

internal table it_mara having only three fields,so declare internal table with

5 fields and then use the inner join ,using inner join is not that hard rajeev

just look at the values which you want to retrieve.before using inner join

have a look at the concept clearly so that you may not confuse,,

try out this code this may probably help you.

REPORT ZMM_DETAIL.

data: begin of it_mara occurs 0,

matnr like mara-matnr,

matkl like mara-matkl,

mtart like mara-mtart,

werks like marc-werks,

ekgrp like marc-ekgrp,

end of it_mara.

select A~matnr

A~matkl

A~mtart

B~werks

B~ekgrp

into table it_mara

from mara as A

inner join marc as B

on A_{matnr eq B}matnr

where A~mtart eq 'HALB'.

HERE A REFERS TO MARA TABLE AND

B REFERS TO MARC.

that is why i used" mara as A inner join marc as B "in

the select query.

plz reward if you finf this helpful,

thanks and regards,

srikanth tulasi.

Edited by: srikanth tulasi on Jan 14, 2008 5:57 AM