topic Inner Join in Application Development and Automation Discussions

Inner Join

Former Member — Tue, 23 Jan 2007 13:32:03 GMT

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

Re: Inner Join

Former Member — Tue, 23 Jan 2007 14:25:43 GMT

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

Re: Inner Join

Former Member — Tue, 23 Jan 2007 14:32:45 GMT

Hi Rob,

thanks for your prompt reply. The data from BVOR will be used to further data retrival.

I will try on your binary read in this report. Thanks a lot!

Re: Inner Join

Former Member — Tue, 23 Jan 2007 14:43:47 GMT

OK - but I go back to my original question - is the BVOR value different from that in BKPF?

Also - BVORG is the first filed of the primary key for this table, so you will not be using an index to read it.

Rob

Re: Inner Join

Former Member — Tue, 23 Jan 2007 15:20:56 GMT

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

Re: Inner Join

Former Member — Fri, 26 Jan 2007 17:53:31 GMT

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.