topic Re: inner join in Application Development and Automation Discussions

inner join

Former Member — Mon, 25 Dec 2006 07:50:53 GMT

hi all,

i have a problem: i have a inner join code and it not bring me the right selection.

my problem is that in table coep it bring me the wrong WOGBTR.

for the first row it bring the data ok and the second all the data ok except for the

WOGBTR that it take it from the first row.

any help will be appreciated.

Dana.

my code is:

data: begin of gs_data,

row_selected(1),

bukrs like bsis-bukrs,

hkont like bsis-hkont,

zuonr like bsis-zuonr,

belnr1 like bkpf-belnr,

belnr like cobk-belnr,

buzei like bsis-buzei,

budat like bsis-budat,

waers1 like skb1-waers,

waers like bsis-waers,

shkzg like bsis-shkzg,

wrbtr like bsis-wrbtr,

wogbtr like coep-wogbtr,

dmbtr like bsis-dmbtr,

zuonr2(10) type n,

zuonr3(10) ,

awkey like bkpf-awkey,

awtyp like bkpf-awtyp,

gjahr like bkpf-gjahr,

dmbe2 like bsis-dmbe2,

end of gs_data,

gt_data like table of gs_data.

select bsis_{bukrs bsis}hkont bsis_{gjahr bsis}zuonr

bsis_{waers bsis}budat bsis_{dmbtr bsis}shkzg

bsis_{bschl bsis}buzei bsis~dmbe2

bkpf_{awkey bkpf}awtyp "bkpf~wrbtr "

bkpf~belnr as belnr1

cobk~belnr

coep~wogbtr

into corresponding fields of table gt_data

from ( bsis

inner join bkpf

on bkpf_{belnr = bsis}belnr

and bkpf_{blart = bsis}blart

and bkpf_{bukrs = bsis}bukrs

and bkpf_{gjahr = bsis}gjahr

inner join cobk

on cobk_{refbn = bkpf}awkey

and cobk_{awtyp = bkpf}awtyp

and cobk_{gjahr = bkpf}gjahr

inner join coep

on coep_{kokrs = cobk}kokrs

and coep_{belnr = cobk}belnr

)

where bsis~bukrs = p_bukrs

and bsis~hkont in so_hkont

and coep~gkont in so_hkont

and bsis~blart in so_blart

and bsis~gjahr = p_gjahr.

Re: inner join

Former Member — Mon, 25 Dec 2006 08:04:44 GMT

Can u make use of field COEP-BUZEI in the join by mapping it with COBK-ALEBZ

<b>inner join coep
on coep~kokrs = cobk~kokrs
and coep~belnr = cobk~belnr
and coep-buzei = cobk-alebz        "this is also required</b>

cause in the table COEP BUZEI is also on of the key field ..try to work on this and check .

try to fetch the entries of COEP based on all the keys of the table this is important when u r fetching the line items

regards,

vijay

Re: inner join

Former Member — Mon, 25 Dec 2006 08:31:29 GMT

thank you vijay but its not working.

Re: inner join

Former Member — Mon, 25 Dec 2006 08:48:17 GMT

Do one thing ..

You know that for a particular criteria header is correct and the item is wrong .

So just make ur inner join work for that single Criteria on line items by providing only one input ..

Now make necessary adjustments in the code till this value is being fetched based on the table entires for that single record ..

once it works for small ranges in your select criteria then slowly expand the range .

p_bukrs

and bsis~hkont in so_hkont

and coep~gkont in so_hkont

and bsis~blart in so_blart

and bsis~gjahr = p_gjahr.

in this give only one value for so_hkont and so_blart

and check ur select criteria..

regards,

vijay

Re: inner join

Former Member — Mon, 25 Dec 2006 10:35:00 GMT

thank you very much,

apperantly the whole select is wrong so i have to do all thing from the beginning.

thank you,

dana.