INNER JOIN

[Former Member]

Hi,

I want o write the program like this. Plz suggest.

Selection screen should be customer numbr, sales document date and sales document type.

Now i want kunnr, name, sales doc date, doc category, item, matnr, qty, netpr, value and join MAKT table.

Plz suggest me how to go about this.

Thanks

[Former Member]

Hi Rams

<b><u>Inner join</u></b>The data that can be selected with a view depends primarily on whether the view implements an inner join or an outer join. With an inner join, you only get the records of the cross-product for which there is an entry in all tables used in the view.

<u><b>Check this sample code</b></u>

data: begin of wa_mkpfmseg,
      mblnr like mkpf-mblnr,
      mjahr like mkpf-mjahr,
      zeile like mseg-zeile,
      bukrs like mseg-bukrs
        bwart like mseg-bwart,
        budat like mkpf-budat,
      cputm like mkpf-cputm,
        matnr like mseg-matnr,
     werks like mseg-werks,
        ebeln like mseg-ebeln,
        ebelp like mseg-ebelp,
        erfmg like mseg-erfmg,
        end of wa_mkpfmseg.

data ht_mkpfmseg like hashed table of wa_mkpfmseg
         with unique key mblnr mjahr zeile
       with header line.

data: begin of wa_mkpfmsegSel,
        budat like mkpf-budat,
      cputm like mkpf-cputm,
        matnr like mseg-matnr,
      werks like mseg-werks,
        ebeln like mseg-ebeln,
        ebelp like mseg-ebelp,
        erfmg like mseg-erfmg,
        end of wa_mkpfmseg.


data ht_mkpfmsegSel like hashed table of wa_mkpfmsegSel
         with unique key budat cputm matnr werks ebeln ebelp 
       with header line.

** change your select sentence to look like this:
  select mkpf~mblnr
              mkpf~mjahr
              mseg~zeile
            mseg~bukrs 
           mseg~bwart
        MKPF~BUDAT MKPF~CPUTM
             MSEG~MATNR MSEG~WERKS MSEG~EBELN 
             MSEG~EBELP  MSEG~ERFMG
                  INTO  TABLE ht_mkpfmseg
                  FROM MKPF INNER JOIN MSEG
                       ON   mkpf~mandt = mseg~mandt 
                  AND    MKPF~MBLNR = MSEG~MBLNR
                  AND MKPF~MJAHR = MSEG~MJAHR
                 where mkpf~budat > '20040721'.

  loop at ht_mkpfmseg.
                check ht_mkpfmseg-bukrs = '1733' and ht_mkpfmseg-bwart = '101'          
                read table ht_mkpfmsegsel with table key   
                     budat = ht_mkpfmseg-budat
                     cputm = ht_mkpfmseg-cputm                                                                                
matnr  = ht_mkpfmseg-matnr          
                     werks  = ht_mkpfmseg-werks                                                   

                     ebeln = ht_mkpfmseg-ebeln   
                     ebelp  = ht_mkpfmseg-ebelp
                transporting erfmg.
                if sy-subrc <> 0.
                   move-corresponding ht_mkpfmseg to ht_mkpfmsegsel.
                   insert table ht_mkpfmsegsel.
                else.
                        ht_mkpfmsegSel-budat = ht_mkpfmseg-budat.
                        ht_mkpfmsegSel-cputm = ht_mkpfmseg-cputm.
                        ht_mkpfmsegSel-matnr = ht_mkpfmseg-matnr,
                        ht_mkpfmsegSel-werks = ht_mkpfmseg-werks.
                        ht_mkpfmsegSel-ebeln = ht_mkpfmseg-ebeln.
                        ht_mkpfmsegSel-ebelp = ht_mkpfmseg-ebelp.
                        add ht_mkpfmseg-erfmg to ht_mkpfmsegSel-erfmg.
                        modify table ht_mkpfmsegSel transporting erfmg.
                endif.
  endloop.
  " at this point ht_mkpfmsegSel has the data collected that you want. 
  loop at ht_mkpfmsegSel.
        .... Here put the code between your select ... endselect.
   endloop.

<u><b>Check this for joining 3 tables</b></u>

 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.  

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

Reward all helpfull answers

Regards

Pavan

[Former Member]

Hi

You join the tables VBAK VBAP with VBELN field and VBAP-MATNR field with MAKT-MATNR field and fetch the data

sample select

select a_{vbeln b}posnr c~maktx into table itab

from vbak as a join vbap as b on a_{vbeln = b}vbeln

join makt as c on b_{matnr = c}matnr

where a~kunnr in s_kunnr and

a~audat in s_audat and

a~auart in s_auart and

c~spras = sy-langu.

<b>Reward points for useful Answers</b>

Regards

Anji