Skip to Content
0
Former Member
Jan 23, 2007 at 01:32 PM

Inner Join

146 Views

Hi!

I have a report that require 1 to many relationship in 4 tables: BKPF, BSIS, T012K, T012.

Now I would like to join table BVOR.

Techical spec is as below:

1. Get (BUKRS, BELNR, GJAHR, HKONT) from BSIS with conditions BUKRS = parameter-BUKRS, HKONT between '750000' to '759999', GJAHR = select_option-GJAHR and BELNR = select_option-BELNR.
2. Get (BUKRS, BELNR, GJAHR, BVORG, HKONT) from BKPF with conditions BUKRS = BSIS-BUKRS, GJAHR = BSIS-GJAHR, BELNR = BSIS-BELNR AND BKTXT = ('&W&' OR '&w&').
3. Get (BANKN, HBKID) from T012K with conditions BUKRS = BKPF-BUKRS, HKONT = BKPF-HKONT.
4. Get (BANKS) from T012 with conditions BUKRS = BKPF-BUKRS, HBKID = T012K~HBKID.
5. Get (BUKRS, BELNR, GJAHR, BVORG) from BVOR with conditions BUKRS <> BKPF-BUKRS, BELNR <> BELNR, BVORG = BKPF-BVORG.

  SELECT BSIS~BUKRS BSIS~BELNR BSIS~GJAHR BKPF~HKONT
         BKPF~BVORG T012K~BANKN T012K~HBKID 
         INTO CORRESPONDING FIELDS OF TABLE ITAB
         FROM BSIS INNER JOIN BKPF  ON  BSIS~BUKRS = BKPF~BUKRS
                                    AND BSIS~BELNR = BKPF~BELNR
                                    AND BSIS~GJAHR = BKPF~GJAHR
                   INNER JOIN T012K ON  T012K~BUKRS = BKPF~BUKRS
                                    AND T012K~HKONT = BKPF~HKONT
                   INNER JOIN T012  ON  T012~BUKRS = BKPF~BUKRS
                                    AND T012~HBKID = T012K~HBKID
                   WHERE BSIS~BUKRS = P_BUKRS  "parameter
                     AND BSIS~BELNR IN S_BELNR "select option
                     AND BSIS~GJAHR IN S_GJAHR "select option
                     AND BSIS~HKONT BETWEEN '0000750000' AND '0000759999'
                     AND BKPF~BKTXT EQ '&W&' OR
                         BKPF~HKONT EQ '&w&'.

Code above join tables BSIS, BKPF, T012K and T012. Can I join BVOR table too? But in BVOR, I need to store the BUKRS, BELNR and GJAHR. If I add 3 more fields to my ITAB, I have to name the field like BUKR1, BELN1 and GJAH1 so that data from BVOR won't overwrite the data get from BSIS. Is it possible?

- If it is not possible, how should I write the code (1 to many). Should I loop ITAB and select data from BVOR and store the data to another internal table? 
- How if I would like to join both internal table?

Please kindly give me suggestion as I wish this program can get better performance than having inner loop. Thanks a lot!

Best regards,

ying ying