Skip to Content
author's profile photo Former Member
Former Member

Inner Join

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

Add a comment
10|10000 characters needed characters exceeded

Related questions

2 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Jan 23, 2007 at 02:25 PM

    I don't understand why you want to get BVOR-BVORG. You already are getting BKPF-BVORG. Will they be different?

    But in any event, I wouldn't add complexity to this select. I'd do a separate select into an internal table for BVOR and use a binary read to get this data.

    Rob

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member
      Hi Rob,
      
       I need to get inter-company document from BVOR. So, I need the conditions to be 
      BUKRS NE BKPF-BUKRS. BELNR NE BKPF-BELNR and BVORG EQ BKPF-BVORG.
       This spec was written by FICO analyst. I have no accounting background and new to 
      ABAP, so i hope I can learn from this forum in order to write a better performance report.
      
      After I get all the data on above. I will need to get Invoices Paid from BSAK and Advance
      Payment Details from BSIK. 
      1. To get Invoice Paid from BSAK: conditions are BUKRS = BKPF-BUKRS, AUGDT = 
          BKPF-BUDAT, AUGBL = BKPF-BELNR and BELNR <> BKPF-BELNR, if cannot get,
          conditions change to BUKRS = BVOR-BUKRS, AUGDT = BKPF-BUDAT, AUGBL = 
          BVOR-BELNR and BELNR <> BVOR-BELNR
      2. To get Advance Payment Details from BSIK: conditions are BUKRS = BKPF-BUKRS,
          BELNR = BKPF-BELNR and GJAHR = BKPF-GJAHR. If cannot get, conditions 
          change to BUKRS = BVOR-BUKRS, BELNR = BVOR-BELNR and GJAHR = 
          BVOR-GJAHR.
      This is the reason I need to get data from BVOR too.
      
      cheers
      
  • author's profile photo Former Member
    Former Member
    Posted on Jan 26, 2007 at 05:53 PM

    If you find that your inner join is not performing well, you could consider breaking it up into individual select-statements. First select from BKPF, and use the reduced list to then select from BSIS and the others.

    Further, check the amount of data in T012 and T012K tables. If it is not much, then have two separate select-statements to select data from these two tables into separate internal tables, and READ the internal table to get the data for each BSIS record.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.