topic Re: join between tables in Application Development and Automation Discussions

join between tables

Former Member — Thu, 08 Mar 2007 22:29:18 GMT

Hello all,

I am working on a report now. I was just wondering if someone could suggest a good efficient join between the tables.

Basically I would be joining VBRK and VBRP which is straight forward. But on the selection screen I have some selections as Sold-to customer(S_KUNNR_SO) Sold-to name(S_NAME1_SO) and ship-to customer(S_KUNNR_SH) and ship-to name (S_NAME1_SH)

The links between VBRP and sold-to customer, VBRP and ship-to customer are

sold-to

VBRP-AUBEL = VBAK-VBELN and VBAK-KUNNR = KNA1-KUNNR

Ship-to

VBRP-AUBEL = VBPA-VBELN and VBPA-KUNNR = KNA1-KUNNR

I was looking for a join between VBRK, VBRP, VBAK, VBPA and KNA1.

any suggestions please......

Re: join between tables

Former Member — Thu, 08 Mar 2007 22:47:04 GMT

Here you go

Join VBRK & VBRP on VBELN, VBRK-KUNAG is sold to party

Join VBRK & VBPA on VBELN and VBPA-PARW = 'WE' , get VBPA-KUNNR (ship-to party)

No need to go to VBAK if you just need sold-to and ship-to information. Also If you don't need item level information only VBRK and VBPA will suffice

Join VBRK & VBPA on VBELN and VBPA-PARW = 'WE' , get VBPA-KUNNR (ship-to party) and VBRK-KUNAG (sold-to)

Re: join between tables

RichHeilman — Thu, 08 Mar 2007 23:27:57 GMT

I'm not 100% comfortable with this, but it is a start none the less. It works in my system, performance is tolerable, but when selecting on just a name, it could be really bad.



report zrich_0001 .

tables: kna1.

types: begin of ttab,
       vbeln type vbrk-vbeln,
       KUNAG type vbrk-kunag,
       aubel type vbrp-aubel,
       name1 type kna1-name1,
       kunn2 type vbpa-kunnr,
       name2 type kna1-name1,
       end of ttab.

types: begin of tvbpa,
       vbeln type vbpa-vbeln,
       parvw type vbpa-parvw,
       kunnr type vbpa-kunnr,
       name1 type kna1-name1,
       end of tvbpa.

data: ivbpa type table of tvbpa.
data: xvbpa like line of ivbpa.

data: itab type table of ttab.
data: wa like line of itab.

select-options: s_kunnrs for kna1-kunnr.
select-options: s_name1s for kna1-name1.
select-options: s_kunnrh for kna1-kunnr.
select-options: s_name1h for kna1-name1.

start-of-selection.

  select vbrk~vbeln vbrk~kunag vbrp~aubel kna1~name1
        into corresponding fields of table itab
            from vbrk
              inner join vbrp
                 on vbrk~vbeln = vbrp~vbeln
              inner join kna1
                 on vbrk~KUNAG = kna1~kunnr
                      where vbrk~KUNAG in s_kunnrs
                        and kna1~name1 in s_name1s.

  check not itab[] is initial.
  sort itab ascending by vbeln aubel.
  delete adjacent duplicates from itab.

  select vbpa~vbeln vbpa~parvw vbpa~kunnr
         kna1~name1
           into table ivbpa
              from vbpa
                inner join kna1
                 on vbpa~kunnr = kna1~kunnr
                     for all entries in itab
                       where vbpa~vbeln = itab-aubel
                         and vbpa~kunnr in s_kunnrh
                         and vbpa~parvw = 'WE'
                         and kna1~name1 in s_name1h.


  loop at itab into wa.

    read table ivbpa into xvbpa with key vbeln = wa-aubel.
    if sy-subrc <> 0.
      delete itab index sy-tabix.
      continue.
    endif.

    wa-kunn2 = xvbpa-kunnr.
    wa-name2 = xvbpa-name1.
    modify itab from wa index sy-tabix.

  endloop.

Regards,

RIch Heilman

Re: join between tables

Former Member — Thu, 08 Mar 2007 23:35:12 GMT

Thanks Rich for the sample code. I will think about your logic and proceed.