topic Re: table join in Application Development and Automation Discussions

table join

Former Member — Thu, 25 Sep 2008 06:03:51 GMT

hi guru's,

i have two internal tables it_bkpf it_mkpf in both awkey is the comman fields containing data now i have to retrive the data from it_bkpf .

can any body tell me how to do this.

Thanks & Best Regards,

Rakhi.

Re: table join

Sandeep_Panghal — Thu, 25 Sep 2008 06:09:06 GMT

Hello,

You want to retrieve data from it_bkpf based on AWKEY ?

If this is the case then loop at it_bkpf.

read it_mkpf with key awkey = it_bkpf-awkey.

IF sy-subrc eq 0.

continue.

else.

delete table it_bkpf .

endif.

endloop.

Hope this helps.

Regards,

Sandeep

Re: table join

sagarmehta — Thu, 25 Sep 2008 06:36:50 GMT

Hi Rakhi,

do you want to read data from table "it_bkpf" on the basis of the data in "it_mkpf" table??

If so then,

LOOP AT it_mkpf INTO wa_mkpf.
    LOOP AT it_bkpf INTO wa_bkpf WHERE awkey = wa_mkpf-awkey.
         "Do the processing that u wish to perform...
   ENDLOOP.
ENDLOOP.

Now if there is only a 1-to-1 relationship between the two tables then u can modify the above code:-

LOOP AT it_mkpf INTO wa_mkpf.
    READ TABLE it_bkpf INTO wa_bkpf WITH TABLE KEY awkey = wa_mkpf-awkey.
         "Do the processing that u wish to perform...
   ENDLOOP.
ENDLOOP.

Hope this helps...

Regards,

Sagar.

Re: table join

Former Member — Thu, 25 Sep 2008 08:16:05 GMT

hi sandeep,

it is working but it taking very long time to process the loop .

is there any way to process the loop fast. can u please tell me.

Thanks & Best Regards,

Praveen.

Re: table join

JozsefSzikszai — Thu, 25 Sep 2008 08:20:26 GMT

for performance improvement:

SORT it_mkpf BY awkey.

loop at it_bkpf.

read it_mkpf with key awkey = it_bkpf-awkey BINARY SEARCH.

Re: table join

Sandeep_Panghal — Thu, 25 Sep 2008 08:21:05 GMT

Hi praveen,

The processing time depends on the number of entries .

Describe table it_bkpf lines lv_bkpf.

Describe table it_mkpf lines lv_mkpf.

Now depening on the number of entries in both the tables u can decide the loop .

By the way , how many records you are processing ?

Hope this helps.

Regards,

Sandeep

Re: table join

Former Member — Thu, 25 Sep 2008 08:28:55 GMT

hi sandeep,

i am processing 175070 fields in the loop.

can u please tell me clearly what should i do.

Thanks & Resgards,

Praveen Rakhi.

Re: table join

Sandeep_Panghal — Thu, 25 Sep 2008 08:32:27 GMT

Hello ,

175000 -->That should not be problem .

Also try what Eric explained .

Sort it_mkpf by awkey.

Loop at it_bkpf.

read it_mkpf with key awkey = it_bkpf-awkey binary search.

if sy-subrc eq 0.

porcessing here as earlier...

endloop.

regards,

Sandeep

Re: table join

Former Member — Thu, 25 Sep 2008 08:52:54 GMT

hi sandeep and eric,

i have tried by sorting the mkpf-awkey. after processing the loop it_bkpf table is empty. but i alredy checked in both tables it_bkpf-awkey and it_mkpf-awkey has one comman field is there.

i want that filed.but it_bkpf is empty.

please tell me.

Tahnks & Best Regards,

Praveen.

Re: table join

Sandeep_Panghal — Thu, 25 Sep 2008 08:59:02 GMT

This message was moderated.

Re: table join

Former Member — Thu, 25 Sep 2008 09:06:14 GMT

hi sandeep,

in bellow is the my coding,

please look at this and help me.

&----

*& Report ZRMMR1MDI

&----

report zrmmr1mdi.

tables : rbkp,bkpf,mkpf.

type-pools : slis.

data : begin of it_mkpf occurs 0,

mblnr like mkpf-mblnr,

mjahr like mkpf-mjahr.

data : awkey like bkpf-awkey,

end of it_mkpf.

data : begin of it_rbkp occurs 0,

belnr like rbkp-belnr,

gjahr like rbkp-gjahr,

blart like rbkp-blart,

bldat like rbkp-bldat,

budat like rbkp-budat,

cpudt like rbkp-budat,

bukrs like rbkp-bukrs.

data : awkey1 like bkpf-awkey,

end of it_rbkp.

data : begin of it_bkpf occurs 0,

belnr like bkpf-belnr,

awkey like bkpf-awkey,

end of it_bkpf.

data : begin of it_bsis occurs 0,

bukrs like bsis-bukrs,

belnr like bsis-belnr, "Document Number

hkont like bsis-hkont, " gl code

kostl like bsis-kostl, " cost center

valut like bsis-valut, " value date

zuonr like bsis-zuonr, " assignment

sgtxt like bsis-sgtxt, " item text.

end of it_bsis.

data : wa_rbkp like it_rbkp .

data : wa_mkpf like it_mkpf.

data : wa_bkpf like it_bkpf .

data : wa_bsis like it_bsis.

data : wa_output type zinvoice,

it_output type standard table of zinvoice.

data : v_page type string,

v_date(10) type c,

wa_heading type slis_listheader,

i_heading type standard table of slis_listheader.

data : so_enste_l1(4) type c,

so_enste_l2(2) type c,

so_enste_l3(2) type c,

so_enste_h1(4) type c,

so_enste_h2(2) type c,

so_enste_h3(2) type c,

so_enste1(10) type c,

so_enste2(10) type c.

selection-screen begin of block selection.

select-options : so_belnr for rbkp-belnr,

so_bukrs for rbkp-bukrs,

so_bldat for rbkp-bldat,

so_budat for rbkp-budat,

so_gjahr for rbkp-gjahr,

so_cpudt for rbkp-cpudt.

selection-screen end of block selection.

perform retrivedata.

perform concotenaterbkp.

perform processdata.

&----

*& Form retrivedata

&----

text

----

--> p1 text
<-- p2 text

----

form retrivedata .

select belnr gjahr blart bldat budat cpudt bukrs into corresponding

fields of table it_rbkp from rbkp

where

belnr in so_belnr and

bukrs in so_bukrs and

bldat in so_bldat and

budat in so_budat and

cpudt in so_cpudt and

gjahr in so_gjahr.

select belnr awkey from bkpf into corresponding fields of table

it_bkpf.

select mblnr mjahr from mkpf into corresponding

fields of table it_mkpf.

*select belnr hkont kostl valut zuonr sgtxt from bsis into

*corresponding

*fields of table it_bsis.

endform. " retrivedata

&----

*& Form processdata

&----

text

----

--> p1 text
<-- p2 text

----

&----

*& Form procesdata

&----

text

----

--> p1 text
<-- p2 text

----

form concotenaterbkp .

loop at it_rbkp.

concatenate it_rbkp-belnr it_rbkp-gjahr into it_rbkp-awkey1.

modify it_rbkp transporting awkey1.

endloop.

loop at it_mkpf.

concatenate it_mkpf-mblnr it_mkpf-mjahr into it_mkpf-awkey.

modify it_mkpf transporting awkey.

endloop.

endform. " concotenaterbkp

&----

*& Form retrivebelnr

&----

text

----

--> p1 text
<-- p2 text

----

&----

*& Form processdata

&----

text

----

--> p1 text
<-- p2 text

----

form processdata .

sort it_mkpf by awkey.

loop at it_bkpf .

read table it_mkpf with key awkey = wa_bkpf-awkey binary search.

if sy-subrc eq 0.

continue.

else.

delete it_bkpf.

endif.

endloop.

endform.

Thanks & Best Regards,

Praveen.

Re: table join

Sandeep_Panghal — Thu, 25 Sep 2008 09:17:22 GMT

Hi ,

Try this:

&----

*& Report ZRMMR1MDI

&----

REPORT zrmmr1mdi.

TABLES : rbkp,bkpf,mkpf.

TYPE-POOLS : slis.

DATA : BEGIN OF it_mkpf OCCURS 0,

mblnr LIKE mkpf-mblnr,

mjahr LIKE mkpf-mjahr,

awkey LIKE bkpf-awkey,

END OF it_mkpf.

DATA : BEGIN OF it_rbkp OCCURS 0,

belnr LIKE rbkp-belnr,

gjahr LIKE rbkp-gjahr,

blart LIKE rbkp-blart,

bldat LIKE rbkp-bldat,

budat LIKE rbkp-budat,

cpudt LIKE rbkp-budat,

bukrs LIKE rbkp-bukrs,

awkey LIKE bkpf-awkey,

END OF it_rbkp.

DATA : BEGIN OF it_bkpf OCCURS 0,

belnr LIKE bkpf-belnr,

awkey LIKE bkpf-awkey,

END OF it_bkpf.

DATA : BEGIN OF it_bsis OCCURS 0,

bukrs LIKE bsis-bukrs,

belnr LIKE bsis-belnr, "Document Number

hkont LIKE bsis-hkont, " gl code

kostl LIKE bsis-kostl, " cost center

valut LIKE bsis-valut, " value date

zuonr LIKE bsis-zuonr, " assignment

sgtxt LIKE bsis-sgtxt, " item text.

END OF it_bsis.

DATA : wa_rbkp LIKE it_rbkp,

wa_mkpf LIKE it_mkpf,

wa_bkpf LIKE it_bkpf,

wa_bsis LIKE it_bsis.

DATA : wa_output TYPE zinvoice,

it_output TYPE STANDARD TABLE OF zinvoice.

DATA : v_page TYPE string,

v_date(10) TYPE c,

wa_heading TYPE slis_listheader,

i_heading TYPE STANDARD TABLE OF slis_listheader.

DATA : so_enste_l1(4) TYPE c,

so_enste_l2(2) TYPE c,

so_enste_l3(2) TYPE c,

so_enste_h1(4) TYPE c,

so_enste_h2(2) TYPE c,

so_enste_h3(2) TYPE c,

so_enste1(10) TYPE c,

so_enste2(10) TYPE c.

SELECTION-SCREEN BEGIN OF BLOCK selection.

SELECT-OPTIONS : so_belnr FOR rbkp-belnr,

so_bukrs FOR rbkp-bukrs,

so_bldat FOR rbkp-bldat,

so_budat FOR rbkp-budat,

so_gjahr FOR rbkp-gjahr,

so_cpudt FOR rbkp-cpudt.

SELECTION-SCREEN END OF BLOCK selection.

PERFORM retrivedata.

PERFORM concotenaterbkp.

PERFORM processdata.

&----

*& Form retrivedata

&----

text

----

--> p1 text
<-- p2 text

----

FORM retrivedata .

SELECT belnr gjahr blart bldat budat cpudt bukrs INTO CORRESPONDING

FIELDS OF TABLE it_rbkp FROM rbkp

WHERE

belnr IN so_belnr AND

bukrs IN so_bukrs AND

bldat IN so_bldat AND

budat IN so_budat AND

cpudt IN so_cpudt AND

gjahr IN so_gjahr.

SELECT belnr awkey FROM bkpf INTO TABLE it_bkpf.

SELECT mblnr mjahr FROM mkpf INTO corresponding fields of TABLE it_mkpf.

*select belnr hkont kostl valut zuonr sgtxt from bsis into

*corresponding

*fields of table it_bsis.

ENDFORM. " retrivedata

&----

*& Form processdata

&----

text

----

--> p1 text
<-- p2 text

----

&----

*& Form procesdata

&----

text

----

--> p1 text
<-- p2 text

----

FORM concotenaterbkp .

LOOP AT it_rbkp.

CONCATENATE it_rbkp-belnr it_rbkp-gjahr INTO it_rbkp-awkey1.

MODIFY it_rbkp TRANSPORTING awkey1.

ENDLOOP.

LOOP AT it_mkpf.

CONCATENATE it_mkpf-mblnr it_mkpf-mjahr INTO it_mkpf-awkey.

MODIFY it_mkpf TRANSPORTING awkey.

ENDLOOP.

ENDFORM. " concotenaterbkp

&----

*& Form retrivebelnr

&----

text

----

--> p1 text
p1 text
<-- p2 text

----

FORM processdata .

SORT it_mkpf BY awkey.

sort it_bkpf by awkey.

LOOP AT it_bkpf INTO wa_bkpf.

READ TABLE it_mkpf into wa_mkpf WITH KEY awkey = wa_bkpf-awkey BINARY SEARCH.

IF sy-subrc EQ 0.

CONTINUE.

ELSE.

DELETE TABLE it_bkpf FROM wa_bkpf.

ENDIF.

ENDLOOP.

Regards,

Sandeep

ENDFORM. "processdata

Re: table join

Former Member — Thu, 25 Sep 2008 09:30:15 GMT

hi sandeep,

its working fine. very very thnaks.

Thanks & Best Regards,

Praveen.