join on two tables mseg and bsim

[Former Member]

hi

i have to select data from both these tables based on select-option datum as basim-budat and sakto as mseg-sakto.

base on these two elect-options i have to fetch material from these tables . where both datum and sakto conditions match.

Thanks

[Former Member]

any ideas for this select query ?

[Former Member]

Hi,

First of all, identify the common fields b/w these two table . then link it with parameter which u r taking from the user.

cheers !

[Former Member]

HI

I applied this logic without join:

SELECT BUDAT MATNR FROM BSIM INTO TABLE ITAB1 WHERE BUDAT IN DATUM.

LOOP AT ITAB1.

SELECT MATNR SAKTO FROM MSEG INTO CORRESPONDING FIELDS OF TABLE ITAB2 WHERE MATNR = ITAB1-MATNR AND SAKTO IN SAKTO.

MOVE ITAB1-BUDAT TO ITAB2-BUDAT.

MOVE ITAB1-MATNR TO ITAB2-MATNR.

APPEND ITAB2.

ENDLOOP.

LOOP AT ITAB2.

MOVE ITAB2-BUDAT TO ITAB3-BUDAT.

MOVE ITAB2-MATNR TO ITAB3-MATNR.

MOVE ITAB2-SAKTO TO ITAB3-SAKTO.

append itab3.

ENDLOOP.

but here in first internal table ..itab its showing material based on user date

In second table its showing material with g/l based on user selection.

But here date field is showing 0 value in itab3.

[raymond_giuseppi]

One program i wrote sometime ago had the following logic :

select * from mbew into table t_mbew

for all entries in t_mara

where matnr = t_mara-matnr

and bwkey in s-bwkey

and bwtar in p-bwtar

and ( bwtty = space or ( bwtty <> space and bwtar <> space ) ).

sort t_mbew by matnr.

select * from mseg

join mkpf on mkpf_{mblnr = mseg}mblnr and

mkpf_{mjahr = mseg}mjahr

into table t_mseg_mkpf

for all entries in t_mbew

where mkpf~budat >= p-budat-low

and mseg~werks in s-werks

and mseg~bwtar = t_mbew-bwtar

and mseg~matnr = t_mbew-matnr.

select * from bsim

into table t_bsim

for all entries in t_mbew

where budat >= p-budat-low

and bwkey in s-bwkey

and bwtar = t_mbew-bwtar

and matnr = t_mbew-matnr.

loop at t_mbew into mbew.

loop at t_bsim into bsim

where matnr = mbew-matnr

and bwkey = mbew-bwkey

and bwtar = mbew-bwtar.

(...)

endloop.

loop at t_t001w into t001w

where bwkey = mbew-bwkey.

loop at t_mseg_mkpf

where mseg-matnr = mbew-matnr

and mseg-werks = t001w-werks

and mseg-bwtar = mbew-bwtar.

(...)

endloop.

endloop.

endloop.

Hope this may help you.

Regards

[Former Member]

Hi Raymond

can you post your this complete program here.

Regards

Anu

[Former Member]

Hi Anu,

First of all you cant update an internal table without loop. in ur case u r trying to update itab3 while u r using loop at itab2.

If you have to take data from these two table u dont need to use join. u can do it without that.

select *

from bsib

into table it_bsib

WHERE BUDAT IN DATUM.

loop at it_bsib WHERE BUDAT IN DATUM.

it_tab1-MATNR = it_bsib-matnr.

it_tab1-BUDAT = it_bsib-BUDAT.

APPEND IT_TAB1.

CLEAR IT_TAB1.

ENDLOOP.

LOOP AT ITAB1 WHERE MATNR = ITAB1-MATNR.

SELECT SINGLE MATNR SAKTO FROM MSEG INTO( IT_TAB1-MATNR, IT_TAB1-SAKTO)

WHERE SAKTO = P_SAKTO.

MODIFY IT_ITAB1.

CLEAR IT_TAB1.

Note : IT_TAB1 is a structure, in that include all the fields whick u want to append in the internal table.

if u get any problem in this let me. i will try to send entire code.

Regards,

Gulrez Alam

bye.

[Former Member]

Hi Gulrez Alam

i tried your given code ...but its not showing any data for it_itab1-sakto ...means data not picked for this field from mseg.

I will appreciate if you can send me complete code

regards

Anu

[Former Member]

Hi Anu,

U r making report using smart form or by using write statement ?

[raymond_giuseppi]

Look at Thread [BSEG|;

Regards

[Former Member]

Hi Annu Singh,

Try the below logic ok.Then ur problem is solved ok.

tables:bsim, mseg.

select-options: s_budat for bsim-budat ,

s_sakto for mseg-sakto.

select field1( table1= bsim)

filed2(table 1= bsim)

filed3(table 2 = mseg)

filed4(table 2 = mseg)

from bsim as a

inner join mseg as b

on a_{filed = b}filed

where budat in s_budat

and sakto in s_sakto.

one important point to remember is that, in the ON condition we have to mention that field, as the filed that is common to both tables ok..

Reward points if helpful

Kiran Kumar.G.A

Edited by: KIRAN KUMAR on Jan 30, 2008 7:56 AM

[Former Member]

Hi kIRAN

I tried it like this

DATA: BEGIN OF ITAB3 OCCURS 0,

SAKTO LIKE MSEG-SAKTO,

MATNR LIKE MSEG-MATNR,

BUDAT LIKE BSIM-BUDAT,

END OF ITAB3.

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

SELECT-OPTIONS: S_BUDAT FOR BSIM-BUDAT ,

S_SAKTO FOR MSEG-SAKTO.

SELECT MATNR BUDAT SAKTO FROM BSIM AS A

INNER JOIN MSEG AS B

ON A_{MATNR = B}MATNR

INTO TABLE ITAB3

WHERE BUDAT IN S_BUDAT

AND SAKTO IN S_SAKTO.

..Its giving error in first line of select condition it says "column name matnr has two meanings :ABAP/4 open SQL statement"