Joining two tables

[Former Member]

Hi Guys,

I have 2 fields (A and B) in internal table1 filled with data. I have 2 fields (C and D) in internal table2 filled with data. itab1 has data from a transparent table and itab2 has data from a pooled table. So, i can't do an inner join on these tables.

I want to store all 4 fields (already with values) in one internal table. I can either extend one of these tables to accomodate the fields from the other table or have a thid table containing these 4 fields.

The join condition for itab1 and itab2 is B and C. How can i store all the values in one table.

Any help is appreciated.

Thanks.

[Former Member]

Hi Govind,

now let's see:

* say these are your tables:
data: begin of itab1 occurs 0,
        a like ...,
        b like ...,
      end of itab1.
data: begin of itab2 occurs 0,
        c like ...,
        d like ...,
      end of itab2.
* this is your target table:
data: begin of itab3 occurs 0,
        a like ...,
        b like ...,
        d like ...,
      end of itab3.

* now we make the join...
clear itab3.
refresh itab3.

loop at itab1.
  move-corresponding itab1 to itab3.
  loop at itab2 where c = itab1-b.
    move-corresponding itab2 to itab3.
    append itab3.
  endloop.
endloop.

And that's it! (at least I think so!)

I hope it helped. BR,

Alvaro

[Former Member]

Hi Alvaro,

You said ,

<i>* now we make the join...</i>

Is the join possible then? In that case, we need not looped at the internal tables, right?

Regards,

Anand Mandalika.

Message was edited by: Poornanand Mandalika

[Former Member]

Hi everyone,

the point is: the data is already stored in <i>internal</i> tables. If it wasn't, we wouldn't be able to make a join directly from the database tables (for one of them is pooled).

BR,

Alvaro

[Former Member]

Hi Alvaro,

I tried the way you mentioned. In the final output, i have the same record values repeated for all the 100 records in output.

Thanks.

[Former Member]

OOPS! I'm sorry buddy. The mention of the word <i>join</i> had automatically reminded me of the database tables. I'm not sure if we can say that we join two internal tables. How about "merge" ?

[Former Member]

Hi Govind,

:-? But that's the it's supposed to work, isn't it? I mean, for every record in <i>itab1</i>, it searches into <i>itab2</i> by the join field. Since a single record in <i>itab1</i> could yield more than one record in <i>itab2</i>, then you get the inner join.

Or do you want to perform an <b>outer join</b>?

BR,

Alvaro

[Former Member]

BTW, Govind, if the tables that you are taling about are SAP tables, then can you tell us which tables you are using?

Regards,

Anand Mandalika.

[Former Member]

Sorry for the misunderstanding O:-)

As the intention was the itabs to behave just like the dbtabs, I used this terminology too...

Yeah, maybe "merge" should have been better.

[christian_wohlfahrt]

Hi,

something in a different direction: it's allowed to change a pool table into a transparent table.

Technically a modification, but for performance reasons surely a option. If this is an option in your case, that's a different question.

This change will cause at least no harm, also conversion itself (except for locks) should cause no trouble.

Regards,

Christian

[Former Member]

Hi Guys,

I am trying to follow the same way. The keys for both the tables are a and b.

DATA: BEGIN OF itabresult OCCURS 0,

a LIKE pooled-a,

c LIKE pooled-c,

b LIKE trans-b,

d LIKE trans-d,

END OF itabresult.

DATA: BEGIN OF itabpooled OCCURS 0,

a LIKE pooled-a,

b LIKE pooled-b,

c LIKE pooled-c,

END OF itabpooled.

DATA: BEGIN OF itabtrans OCCURS 0,

a LIKE trans-a,

b LIKE trans-b,

d LIKE trans-d,

END OF itabtrans.

SELECT b d FROM trans INTO CORRESPONDING FIELDS OF TABLE itabtrans.

SELECT a c FROM pooled INTO CORRESPONDING FIELDS OF TABLE itabpooled.

LOOP AT itabpooled.

MOVE-CORRESPONDING itabpooled TO itabresult.

LOOP AT itabtrans WHERE b EQ itabpooled-c.

MOVE-CORRESPONDING itabpooled TO itabresult.

APPEND itabresult.

ENDLOOP.

ENDLOOP.

LOOP AT itabresult.

WRITE: / itabresult-a, itabresult-b, itabresult-c, itabresult-d.

ENDLOOP.

Actually, both itabtrans and itabpooled are filled with values. I need to merge both these internal tables. There are 900 records in table trans and 200 records in pooled. The itabresult table has more than 900 records. Can you tell me if this approach is right.

Thanks.

[Former Member]

Hello Everyone,

Finally, i am getting the output after tweaking the code a little. I used the same code which i posted earlier. But, i have a problem during extraction. If i run the code as a program, i am getting anticipated number of records. But, if i run the code in FM, i am getting only limited number of records. What i observed is, i am getting only 1 data package number of records during the extraction. I think it has got to do with DATAPAKID. What may be reason?

Thanks.