01-23-2007 1:32 PM
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
01-23-2007 2: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
01-23-2007 2:32 PM
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!
01-23-2007 2:43 PM
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
01-23-2007 3:20 PM
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
01-26-2007 5: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.