Table Join

[Former Member]

hello,

I am just a novice ABAP programmer and cant get an idea how to join 2 tables. the scenario is:

i have 2 tables with 3 fields:

1. Tab1 - f1a, f1b, f1c

2. Tab2 - f2a, f2b, f3b

1 internal table with 3 fields:

1. itab - f1, f2, f3

the condition is:

if Tab1-f1a = Tab2-f2a and Tab1-f1b = Tab2-f2b

then

itab-f1 = Tab1-f1a

itab-f2 = Tab1-f1b

itab-f3 = Tab1-f1c + Tab2-f2c

appreciate your help. Thanks

PAUL

[Former Member]

Hi,

Try this.

Loop at tab1 into wa_tab1.

read table tab2 into wa_tab2

with key f2a = wa_tab1-f1a.

if sy-subrc eq 0.

itab-f1 = wa_tab1-f1a

itab-f2 = wa_tab1-f1b

itab-f3 = wa_tab1-f1c + wa_tab2-f2c

endif.

endloop.

Sharin.

[Former Member]

Hi Paul,

Paul wrote :

i have 2 tables with 3 fields:
1. Tab1 - f1a, f1b, f1c
2. Tab2 - f2a, f2b, f3b

1 internal table with 3 fields:
1. itab - f1, f2, f3

the condition is:
if Tab1-f1a = Tab2-f2a and Tab1-f1b = Tab2-f2b
then
itab-f1 = Tab1-f1a
itab-f2 = Tab1-f1b
itab-f3 = Tab1-f1c + Tab2-f2c

Make your select query something like this below :

Select * 
    into corresponding fields of itab
   from tab1
    join tab2 on tab1-f1a = tab2f2a
     and tab1-f1b = tab2-f1b.

Regards,

Swapna.

[Former Member]

hi Sharin, thanks for the response.. tab1 and tab2 are DDIC tables cant use loop command, right? i used select statements but it almost made me fooled. stil cant get my desired output.

hi Swapna, ive pattern my script to your supplied code but doesnt generated output. seemed the data didnt pulled from tables to internal table. Any idea?

thanks,

PAUL

[Former Member]

First ,you need to take 2 internal table compatible to Tab1 and Tab2 say, itab1 and itab2 respectively


then select (field list)
          from TAB1
          into  itab1.

and select (field list)
          from TAB2
          into  itab2.


then,   loop at itab1.
            read table itab2 index sy-tabix.

             if itab1-f1a = itab2-f2a and itab1-f1b = itab2-f2b

             itab-f1 = Tab1-f1a
             itab-f2 = Tab1-f1b
             itab-f3 = Tab1-f1c + Tab2-f2c
              
 endif.
endloop.

[Former Member]

Thanks,

But the problem on thess scripts is that it only display the last matches found on condition:

if itab1-f1a = itab2-f2a and itab1-f1b = itab2-f2b

need to display all matches found and add f1c and f2c.

Thanks

PAUL

[Former Member]

Hi, I think that the two tables are ddic table ,so you can't use loop command on those table.

So create three types .

one for TAb1.

one for tab2.

one for itab.

try this and see if this is useful.

----

---

types : begin of t_tab1,

if1a type Tab1-f1a,

if1b type Tab1-f1b,

if1c type Tab1-f1c,

end of t_tab1,

begin of t_tab2,

if2a type Tab2-f2a,

if2b type Tab2-f2b,

if2c type Tab2-f2c,

end of t_tab2,

begin of t_tab3,

f1 type Tab1-f1a,

f2 type Tab1-f1b,

f3 type tab1-f1c,

end of t_tab3.

data : i_tab1 type standard table of t_tab1,

i_tab2 type standard table of t_tab2,

i_tab3 type standard table of t_tab3,

wa_tab1 type t_tab1,

wa_tab2 type t_tab2,

wa_tab3 type t_tab3.

start-of-selection.

select f1a

f1b

f1c

from tab1 into table i_tab1.

select f2a

f2b

f2c from Tab2 into table i_tab2.

if not i-tab1 is initial.

loop at i_tab1 into wa_tab1.

select if2a

if2b

if2c

from i_tab2 into wa_tab3 where wa_tab1-f1a = wa_tab3-f1a

and wa_tab1-f1b = wa_tab3-f1b.

if not wa_tab3 is initial.

wa_tab3-f1 = wa_tab1-f1a.

wa_tab3-f2 = wa_tab1-f1b.

wa_ tab3-f3 = wa_tab1-f3a + wa_tab3-f3. " I added the value in wa_tab3-f3 because it actuall contain the valut in f2b field in Tab2.

append wa_tab3 to i_tab3.

clear wa_tab3.

endif.

endloop.

endif.

----

---

please let me know if it works or not.

[Former Member]

Hi, I think that the two tables are ddic table ,so you can't use loop command on those table.

So create three types .

one for TAb1.

one for tab2.

one for itab.

try this and see if this is useful.

----

---

types : begin of t_tab1,

if1a type Tab1-f1a,

if1b type Tab1-f1b,

if1c type Tab1-f1c,

end of t_tab1,

begin of t_tab2,

if2a type Tab2-f2a,

if2b type Tab2-f2b,

if2c type Tab2-f2c,

end of t_tab2,

begin of t_tab3,

f1 type Tab1-f1a,

f2 type Tab1-f1b,

f3 type tab1-f1c,

end of t_tab3.

data : i_tab1 type standard table of t_tab1,

i_tab2 type standard table of t_tab2,

i_tab3 type standard table of t_tab3,

wa_tab1 type t_tab1,

wa_tab2 type t_tab2,

wa_tab3 type t_tab3.

start-of-selection.

select f1a

f1b

f1c

from tab1 into table i_tab1.

select f2a

f2b

f2c from Tab2 into table i_tab2.

if not i-tab1 is initial.

loop at i_tab1 into wa_tab1.

select if2a

if2b

if2c

from i_tab2 into wa_tab3 where wa_tab1-f1a = wa_tab3-f1a

and wa_tab1-f1b = wa_tab3-f1b.

if not wa_tab3 is initial.

wa_tab3-f1 = wa_tab1-f1a.

wa_tab3-f2 = wa_tab1-f1b.

wa_ tab3-f3 = wa_tab1-f3a + wa_tab3-f3. " I added the value in wa_tab3-f3 because it actuall contain the valut in f2b field in Tab2.

append wa_tab3 to i_tab3.

clear wa_tab3.

endif.

endloop.

endif.

----

---

please let me know if it works or not.

[Former Member]

hi Jayati,

got errors on the scripts you suggested. Mabye better if i will give you the actual tables and fields i am using.

2 tables with their respective fields:

1. KNKK - KKBER, KUNNR, SKFOR

2. S067 - KKBER, KNKLI, OLIKW

i made an internal table:

data: begin of GT_DATA occurs 0,

KKBER type KNKK-KKBER,

KUNNR type KNKK-KUNNR,

KLIMK type KNKK-KLIMK,

SKFOR type KNKK-SKFOR.

the requirements is:

if KNKK-KKBER = S067-KKBER AND KNKK-KUNNR = S067-KNKLI

then,

GT_DATA-SKFOR = KNKK-SKFOR + S067-OLIKW.

all data should be displayed.

Thanks

PAUL

Edited by: Paul Warren Pili on Sep 9, 2008 3:23 AM

[Former Member]

Hi, Paul.

I think there is no matched record.

And you should try to find the matched records manually first.

If you find at least one, there are problems still on your script,

but if there isn't one at all, there is no problem on the several scripts above also.

[Former Member]

Hi Kieat,

Yeah. there is. the problem on looping in internal table is that it only displayed the last found match entry. and i need to find all matched entries.

PAUL

[Former Member]

May be the entry displayed is in the header line of GT_DATA.

Did you debug your scripts?

Are there records in the internal table?

[Former Member]

what ive done is created 2 internal tables:

1. itab1 for KNKK

2. itab2 for S067

then copy all necessary fields from these 2 tables to the internal tables.

and then made a loop..

loop at itab1.

read table itab2 index sy-tabix.

if itab1-KKBER = itab2-KKBER and itab1-KUNNR = itab2-KNKLI.

GT_DATA-KKBER = itab1-KKBER.

GT_DATA-KUNNR = itab1-KUNNR.

GT_DATA-KLIMK = itab1-KLIMK.

GT_DATA-SKFOR = itab1-SKFOR + itab1-SSOBL + itab2-OLIKW + itab2-OFAKW.

append GT_DATA.

endif.

endloop.

here i expected that there should be 3 match found..as ive checked it manually.

(meaning the if statment will be TRUE 3 times)

but unfortunately it only display the last found matched entry.

my desired output would be 3 matches.

[Former Member]

Try this.

loop at itab1.

clear gt_data.

read table itab2 with key KKBER = itab1-KKBER

KNKLI = itab1-KUNNR.

if sy-subrc = 0.

GT_DATA-KKBER = itab1-KKBER.

GT_DATA-KUNNR = itab1-KUNNR.

GT_DATA-KLIMK = itab1-KLIMK.

GT_DATA-SKFOR = itab1-SKFOR + itab1-SSOBL + itab2-OLIKW + itab2-OFAKW.

append GT_DATA.

endif.

endloop.

[Former Member]

Hello kieat,

No Data has been displayed when i pattern to your suggested script.

Anyone can do this using "inner join statement"? looping on internal tables might not working on this case.

thanks,

PAUL

[Former Member]

Hello,

i was able to display all matched entries between 2 tables.

My problem now is i also need to display in my internal tables\ those entries in TAB1 that doesnt match in TAB2.

appreciate your help thanks

PAUL

[Former Member]

• Select data from the two database tables (tab1 and tab2) into internal tables (itab1 and itab2)

SELECT F1A F1B F1C FROM TAB1 INTO ITAB1.

SELECT F2A F2B F2C FROM TAB2 INTO ITAB2.

LOOP AT ITAB1 INTO WA_TAB1.

READ ITAB2 INTO WA_TAB2 WITH KEY F2A = WA_TAB1-F1A AND F2B = WA_TAB1-F1B.

IF SY-SUBRC EQ 0.

WA_TAB2-F2C = WA_TAB2-F2C + WA_TAB1-F1C.

MOVE: WA_TAB1-F1A TO WA_TAB-F3A,

WA_TAB1-F1B TO WA_TAB-F3B,

WA_TAB2-F2C TO WA_TAB-F3C.

APPEND WA_TAB INTO ITAB.

ENDIF.

ENDLOOP

[Former Member]

thanks everyone for the ideas. Problem solved.