topic Re: Join on Internal tables in Application Development and Automation Discussions

Join on Internal tables

Former Member — Tue, 26 Feb 2008 11:01:59 GMT

Hello ppl,

I have 2 internal tables.

I want to write a select query to select 2 fields from one internal table and 1 field from another internal table.

These 3 fields will be selected in a third internal table.

Fields to be selected: gt_mast-matnr

gt_mast-werks

gt_afko-aufld

Into internal table gt_csbe.

How to achieve this?

Please help.

Thanks,

David.

Re: Join on Internal tables

SantoshKallem — Tue, 26 Feb 2008 11:20:41 GMT

use for all entries

Re: Join on Internal tables

Former Member — Tue, 26 Feb 2008 11:25:01 GMT

Hi,

It can achieved by using Inner Join.

Refer the sample code for using Inner Join:

Inner joins using 3 tables

Try this :-


SELECT stpo~stlnr stpo~idnrk mast~matnr mara~mtart stpo~menge  
INTO CORRESPONDING FIELDS OF TABLE zmat1 FROM mast  
JOIN stpo ON stpo~stlnr = mast~stlnr  
JOIN mara ON mara~matnr = mast~matnr  
WHERE stpo~stlty = 'M' "AND stpo~idnrk IN s_matnr  
AND mast~werks = 1000.

Here s_matnr is a select-options on the selection-screen.

Or this.


     Select single Vbrk~Bukrs Vbrk~Kunrg    Vbrk~Vbeln  
                   Vbrk~Fkdat Vbrk~Bstnk_Vf Vbrk~Zterm  
                   Tvzbt~Vtext  
                   Vbak~Vbeln Vbak~Bstdk  
                   Likp~Vbeln Likp~lfdat    Likp~Lfuhr  
       into w_vbrk  
       from vbrk  
      inner join       Tvzbt on Tvzbt~Zterm        = Vbrk~Zterm      and  
                                Tvzbt~Spras        = sy-langu  
      Inner join       Vbfa  as SalesLnk  
                             on SalesLnk~vbeln     = pu_vbeln        and  
                                SalesLnk~vbtyp_v   = c_order  
            inner join Vbak  on Vbak~Vbeln           = SalesLnk~Vbelv 
      Inner join       Vbfa  as DeliveryLnk  
                             on DeliveryLnk~vbeln   = pu_vbeln       and  
                                DeliveryLnk~vbtyp_v = c_Delivery  
            inner join Likp  on Likp~Vbeln          = DeliveryLnk~Vbelv  
      where vbrk~vbeln = pu_Vbeln.

This code locates sales, delivery and payment terms info from a billing document number.

Here, this one also works fine :


select zfpcd~cadivi zfpcd~proforma zfpcd~factura zfpcd~aniofactura  
zfpcd~montousd zfpcd~montoap zfpcd~ebeln zfpcd~inco1  
zfpcd~lifnr lfa1~name1 zcdvs~status zfpcd~conint  
into it_lista  
from zfpcd inner join zcdvs  
on zfpcd~ebeln = zcdvs~ebeln  
and zfpcd~proforma = zcdvs~proforma  
and zfpcd~lifnr = zcdvs~lifnr  
inner join lfa1  
on zfpcd~lifnr = lfa1~lifnr  
where zcdvs~status = '04'.

<REMOVED BY MODERATOR>

Cheers,

Chandra Sekhar.

Edited by: Alvaro Tejada Galindo on Feb 26, 2008 12:43 PM

Re: Join on Internal tables

Former Member — Tue, 26 Feb 2008 11:55:07 GMT

Hi David,

One thing I would like to tell you that you can not use SELECT clause on the internal table, you can use on the databasae table.

Now we will come to your requirement. I guess you are having 2 internal table named gt_mast & gt_afko and you want to add matnr and werks from gt_mast and aufld from gt_afko into new internal table gt_csbe, for that you have to loop any one internal table say gt_mast and read other internal table say gt_afko into the loop using any of the key field.

Only you have to make sure that in both the internal table there should be common key field or unique record so that you can get the proper data into third internal table gt_csbe.

If you provide me the structure of all 3 internal tables with the exact requirement so that I shall help you to get the things done.

<REMOVED BY MODERATOR>

Cheers,

Prashant Raichurkar

Edited by: Alvaro Tejada Galindo on Feb 26, 2008 12:44 PM

Re: Join on Internal tables

Former Member — Tue, 26 Feb 2008 12:02:55 GMT

hi,

try this.


appending lines of i_tab1 to i_tab2.

if fields are not same


loop at i_tab1
  v_tabix = sy-tabix.
  read table i_tab3 with key <key fields of i_tab1>
  if sy-subrc eq 0.
    move corresponding fields i_tab1 to i_tab3.
    modify i_tab3 index v_tabix..
  else.
    move corresponding fields i_tab1 to i_tab3.
    append i_tab3.    
 endif.
endloop.
 
loop at i_tab2
  v_tabix = sy-tabix.
  read table i_tab3 with key <key fields of i_tab2>
  if sy-subrc eq 0.
    move corresponding fields i_tab2 to i_tab3.
    modify i_tab3 index v_tabix..
  else.
    move corresponding fields i_tab2 to i_tab3.
    append i_tab3.    
 endif.
endloop.

<REMOVED BY MODERATOR>

Edited by: Alvaro Tejada Galindo on Feb 26, 2008 12:44 PM

Re: Join on Internal tables

dhruv_shah3 — Tue, 26 Feb 2008 12:03:02 GMT

Hi,

Select field1 field2 from SomeTable into corresponding field of table IT1.

Select field1 from SomeTable into corresponding field of table IT2.

Loop at IT1 into WA1 with key WA1-matnr.

read table IT2 into WA2 with key WA2-matnr = <some common field>

WA_gt_csbe-matnr = WA1_gt_mast-matnr

WA_gt_csbe-werks = WA1_gt_mast-werks

WA_gt_csbe-aufld = WA2-aufld

append WA_gt_csbe to IT_gt_csbe.

endloop.

Thanks & Regards,

Dhruv Shah