10-17-2007 6:41 AM
Hi gurus,
Need your help. I have more than 10,000 entries in BSIS and it took me 5mins to retireve it. Here's my code...
SELECT a~mandt
a~belnr
A~BUZEI
a~mwskz
a~budat
A~SHKZG
A~DMBTR
b~lifnr
B~NAME1
b~ktokk
APPENDING CORRESPONDING
FIELDS OF TABLE lt_tab
FROM bsak AS a
INNER JOIN lfa1 AS b
ON a~lifnr = b~lifnr
WHERE a~bukrs EQ so_ccode
AND A~GJAHR EQ LV_YEAR
AND a~budat IN so_postp
AND MWSKZ IN SO_TXCDE.
SORT lt_tab1 BY lifnr belnr.
DELETE LT_TAB WHERE KTOKK EQ 'UEMP'.
if not lt_tab[] is initial.
SELECT BELNR
MWSKZ
SHKZG
DMBTR
WAERS
HKONT
INTO CORRESPONDING FIELDS OF TABLE LT_TAB2_TEMP
FROM BSIS
for all entries in lt_tab
WHERE BUKRS EQ SO_CCODE
AND hkont ne c_hkont
AND GJAHR EQ LV_YEAR
AND belnr eq lt_tab-belnr
AND MWSKZ IN SO_TXCDE
AND BUDAT IN SO_POSTP.
endif.
10-17-2007 6:48 AM
Hi,
Can you check other alternative of using other table not BSIS as in actual production env you may face more performance issue.
Regards,
Atish
10-17-2007 6:48 AM
Hi,
Can you check other alternative of using other table not BSIS as in actual production env you may face more performance issue.
Regards,
Atish
10-17-2007 7:01 AM
Before deleting the data from lt_tab, sort the internal table.
And also delete the duplicate belnr entries.
As below.
SELECT a~mandt
a~belnr
A~BUZEI
a~mwskz
a~budat
A~SHKZG
A~DMBTR
b~lifnr
B~NAME1
b~ktokk
APPENDING CORRESPONDING
FIELDS OF TABLE lt_tab
FROM bsak AS a
INNER JOIN lfa1 AS b
ON alifnr = blifnr
WHERE a~bukrs EQ so_ccode
AND A~GJAHR EQ LV_YEAR
AND a~budat IN so_postp
AND MWSKZ IN SO_TXCDE.
SORT lt_tab1 BY lifnr belnr.
SORT LT_TAB BY KTOKK.
DELETE LT_TAB WHERE KTOKK EQ 'UEMP'.
SORT LT_TAB BY BELNR.
DELETE ADJACENT DUPLICATES FROM LT_TAB COMPARING BELNR.
if not lt_tab[] is initial.
SELECT BELNR
MWSKZ
SHKZG
DMBTR
WAERS
HKONT
INTO CORRESPONDING FIELDS OF TABLE LT_TAB2_TEMP
FROM BSIS
for all entries in lt_tab
WHERE BUKRS EQ SO_CCODE
AND hkont ne c_hkont
AND GJAHR EQ LV_YEAR
AND belnr eq lt_tab-belnr
AND MWSKZ IN SO_TXCDE
AND BUDAT IN SO_POSTP.
endif.
10-17-2007 7:41 AM
1. you are using "hkont ne c_hkont" in the where condition, as far as I remember NE can not be supported by the index
2. AUGDT & AUGBL are - if I remember correct - in BSIS always 00000000 or space
3. check how ZUONR is filled in your system; if filled in vendor line and G/L account line equal select in the join also ZUONR into lt_tab
so my suggestion on the BSIS code:
SELECT BELNR
MWSKZ
SHKZG
DMBTR
WAERS
HKONT
INTO CORRESPONDING FIELDS OF TABLE LT_TAB2_TEMP
FROM BSIS
for all entries in lt_tab
WHERE BUKRS EQ SO_CCODE
AND hkont in r_hkont "your accounts needed
and augdt EQ '00000000' "please review if they are really always initial
and augbl EQ space "please review if they are really always initial
and zuonr EQ lt_tab-zuonr "only if filled equal as BSAK otherwise not!
AND GJAHR EQ LV_YEAR
AND belnr eq lt_tab-belnr
AND MWSKZ IN SO_TXCDE
AND BUDAT IN SO_POSTP.
10-17-2007 8:48 AM
Thanks Daniel,
But do you think even if they have numbers of gl account, I should include it in the filter?
10-17-2007 12:34 PM
Even without testing yes I think you should.
But I think best way that you try out both options on your system and then choose the better one for the final version of the program.