topic Re: join in Application Development and Automation Discussions

join

Former Member — Sat, 20 Jan 2007 18:02:29 GMT

Hi,

I want to write a simple proram for inner join.

tables: are mara, mard.

Select-options: S_MATNR

Data: Define MAT_STR containing MATNR MTART WERKS LGORT LABST

I have written the code in following way...but it is not working.

I am new to this area.

tables: mara.

tables: mard.

data: mat_str type table of mard-mara with header line.

data: p_matnr type c.

data: p_mtart type c.

data: p_werks type c.

data: p_lgort type c.

data: p_labst type c.

select matnr

mtart

werks

lgort

labst

into ( p_matnr, p_mtart, p_werks, p_lgort, p_labst) from mard inner join mara

on mara ~ matnr = mard ~ matnr

end select.

END-OF-SELECTION.

LOOP AT MAT_STR.

WRITE:/ MAT_STR-MATNR, MAT_STR-WERKS, mat_str-mtart, mat_str-lgort, mat_str-labst.

ENDLOOP.

Thanks in advance.

Re: join

RichHeilman — Sat, 20 Jan 2007 18:11:48 GMT

Here is some modified code.



Types: begin of tma,
           matnr type mara-matnr,
           mtart type mara-mtart,
           werks type mard-werks,
           lgort type mard-lgort,
           labst type mard-labst,
           end of tma.

data: ima type table of tma with header line.

select mara~matnr mara~mtart mard~werks mard~lgort mard~labst
                into table ima
                     from mara 
                             inner join mard 
                                      on mara~matnr = mard~matnr 
                                                 up to 10 rows.    "<- For testing
 

LOOP at ima.
    WRITE:/ ima-MATNR, ima-WERKS, ima-mtart, ima-lgort, ima-labst.
ENDLOOP.

Regards,

RIch Heilman

Re: join

Former Member — Sat, 20 Jan 2007 18:35:53 GMT

Hi,

Please try this.


tables: mara, 
        mard.

select-options: s_matnr for mara-matnr.

data: begin of itab occurs 0,
        matnr like mara-matnr,
        mtart like mara-mtart,
        werks like mard-werks,
        lgort like mard-lgort,
        labst like mard-labst.
data: end of itab.

select a~matnr 
       a~mtart 
       b~werks 
       b~lgort 
       b~labst
into table itab
from mara as a inner join mard as b on a~matnr = b~matnr
where a~matnr in s_matnr.

end-of-selection.

loop at itab.
  write: / itab-matnr, 
           itab-mtart, 
           itab-werks, 
           itab-lgort, 
           itab-labst.
endloop.

Regards,

Ferry Lianto

Re: join

Former Member — Sat, 20 Jan 2007 18:37:54 GMT

Use Query,

Select <data you want > into <Internal table>

from Mara as a

inner join

Mard as b

on a_{matnr = b}matnr.

if sy-subrc = 0.

write: < Data you want to display>.

endif.

Re: join

Former Member — Sat, 20 Jan 2007 18:43:28 GMT

Hi sulogna,

Hope this code helps u.

Tables : mara, mard.

Data : begin of itab occurs 0,

matnr like mara-matnr,

mtart like mara-mtart,

werks like mard-werks,

lgort like mard-lgort,

labst like mard-labst,

end of itab.

Select-options : s_matnr for mara-matnr.

Start-of-selection.

Select mara_{matnr mara}mtart mard_{werks mard}lgort mard_{labst into corresponding fields of table itab from mara inner join mard on mara}matnr = mard_{matnr where mara}matnr in s_matnr.

If sy-subrc ne 0.

Message i001(zmm) with ‘No data exists’.

Endif.

Loop at itab.

Write : / itab-matnr, itab-mtart, itab-werks, itab-lgort,

itab-labst.

Endloop.

Regards....

Arun.

Re: join

Former Member — Sat, 20 Jan 2007 19:03:50 GMT

tables: mara,

mard.

*declare internal tabel first

data : begin of itab occurs 0,

matnr like mara-matnr,

mtart like mara-mtart,

werks like mard-werks,

lgort like mard-lgort,

labst like mard-labst,

end of itab.

*if you want have select-options declare them.

select-options : p_matnr for mara-matnr,

p_werks for mard-werks.

select mara~matnr

mara~mtart

mard~werks

mard~lgort

mard~labst "when u write table u need not to write

"endslect

into table itab

from mara

inner join mard

on mara_{matnr = mard}matnr.

if sy-subrc = 0.

loop at itab.

write : /1 itab-matnr,

20 itab-mtart,

35 itab-werks,

50 itab-lgort,

80 itab-labst.

endloop.

endif.

try this u will get it. you have to declare your internal table proparly when joining two tables we have to write on condition

Re: join

Former Member — Sat, 20 Jan 2007 19:06:28 GMT

tables: mara,

mard.

*declare internal tabel first

data : begin of itab occurs 0,

matnr like mara-matnr,

mtart like mara-mtart,

werks like mard-werks,

lgort like mard-lgort,

labst like mard-labst,

end of itab.

*if you want have select-options declare them.

select-options : p_matnr for mara-matnr,

p_werks for mard-werks.

select mara~matnr

mara~mtart

mard~werks

mard~lgort

mard~labst "when u write table u need not to write

"endslect

into table itab

from mara

inner join mard

on mara_{matnr = mard}matnr

where matnr = p_matnr.

if sy-subrc = 0.

loop at itab.

write : /1 itab-matnr,

20 itab-mtart,

35 itab-werks,

50 itab-lgort,

80 itab-labst.

endloop.

endif.

try this one. you have to declare your itab proparly. while doing join we have to write "ON " CONDITION.

Re: join

Former Member — Sat, 20 Jan 2007 23:23:11 GMT

Hi Rich,

Thanks you so much for your answer.

I have one question. You declare first as "type". Why you write "type" instead of "data"?. And can I take any table name in place of "ima"? Does it indicate any special type of table or any meaning?

Thanks

sulogna

Message was edited by:

sulogna Chatterjee

Re: join

RichHeilman — Sun, 21 Jan 2007 01:13:20 GMT

I use TYPES statement to declare the TYPE, the the DATA statement to declare the internal table IMA. IMA is just the name of the internal table which will have the structure of TMA.

REgards,

RIch Heilman