11-12-2007 5:06 AM
Hi All,
Please find below a select stmt and recommend a better one.
select abelnr abldat abudat amonat ausnam abktxt awaers bracct bwsl bdrcrk bbuzei ctxt50 d~altkt
into table gt_all
from bkpf as a inner join faglflexa as b on abelnr = bdocnr
inner join skat as c on bracct = csaknr
inner join skb1 as d on csaknr = dsaknr
where a~bukrs = p_bukrs and
a~gjahr = p_gjahr and
a~blart = p_blart and
a~budat in s_budat and
a~monat in s_monat and
b~ryear = p_gjahr and
b~rbukrs = p_bukrs and
b~rldnr = '0L' and
b~rvers = '001'.
Reward points awaiting.....
Regards,
Younus
11-12-2007 5:34 AM
HI
don't use JOIN for 4 tables use FOR ALL ENTRIES
because if use JOIN for that 4 tables the join will exit for that tables until the program execution which results in load on database and it will reduce the performance
if use FOR ALL ENTRIES then at once it will retrive the data and there won't be any load on database result in good performance
use like this
START-OF-SELECTION.
SELECT OTYPE
OBJID
RELAT
BEGDA
ENDDA
SCLAS
SOBID FROM HRP1001 INTO TABLE IT_HRP1001
WHERE OTYPE = 'D'
AND OBJID IN S_OBJID
AND BEGDA GE DATE-LOW
AND ENDDA LE DATE-HIGH
AND ( SCLAS = 'E' OR SCLAS = 'ET' ).
IF SY-SUBRC NE 0.
MESSAGE 'NO RECORD FOUND FOR THE GIVEN SELECTION CRITERIA ' TYPE 'E'.
ENDIF.
SELECT OTYPE
OBJID
AEDTM
UNAME
DELET
CANCR
FROM HRP1026
INTO TABLE IT_HRP1026
FOR ALL ENTRIES IN IT_SOBID
WHERE OBJID = IT_SOBID-SOBID
AND ( OTYPE = 'E' OR OTYPE = 'ET' )
AND DELET = 'X' AND
BEGDA GE DATE-LOW AND
ENDDA LE DATE-HIGH.
IF SY-SUBRC EQ 0.
SELECT OBJID
STEXT
FROM HRP1000
INTO TABLE IT_HRP1000
FOR ALL ENTRIES IN IT_SOBID
WHERE OBJID = IT_SOBID-SOBID AND
BEGDA GE DATE-LOW AND
ENDDA LE DATE-HIGH.
SELECT CANCR
CANCRT
FROM T77CRT
INTO TABLE IT_REASON
FOR ALL ENTRIES IN _HRP1000
WHERE CANCR = IT_HRP1026-CANCR
AND LANGU = 'E' .
ENDif.
use in this way FOR ALL ENTRIES
<b>Reward if usefull</b>
11-12-2007 5:34 AM
HI
don't use JOIN for 4 tables use FOR ALL ENTRIES
because if use JOIN for that 4 tables the join will exit for that tables until the program execution which results in load on database and it will reduce the performance
if use FOR ALL ENTRIES then at once it will retrive the data and there won't be any load on database result in good performance
use like this
START-OF-SELECTION.
SELECT OTYPE
OBJID
RELAT
BEGDA
ENDDA
SCLAS
SOBID FROM HRP1001 INTO TABLE IT_HRP1001
WHERE OTYPE = 'D'
AND OBJID IN S_OBJID
AND BEGDA GE DATE-LOW
AND ENDDA LE DATE-HIGH
AND ( SCLAS = 'E' OR SCLAS = 'ET' ).
IF SY-SUBRC NE 0.
MESSAGE 'NO RECORD FOUND FOR THE GIVEN SELECTION CRITERIA ' TYPE 'E'.
ENDIF.
SELECT OTYPE
OBJID
AEDTM
UNAME
DELET
CANCR
FROM HRP1026
INTO TABLE IT_HRP1026
FOR ALL ENTRIES IN IT_SOBID
WHERE OBJID = IT_SOBID-SOBID
AND ( OTYPE = 'E' OR OTYPE = 'ET' )
AND DELET = 'X' AND
BEGDA GE DATE-LOW AND
ENDDA LE DATE-HIGH.
IF SY-SUBRC EQ 0.
SELECT OBJID
STEXT
FROM HRP1000
INTO TABLE IT_HRP1000
FOR ALL ENTRIES IN IT_SOBID
WHERE OBJID = IT_SOBID-SOBID AND
BEGDA GE DATE-LOW AND
ENDDA LE DATE-HIGH.
SELECT CANCR
CANCRT
FROM T77CRT
INTO TABLE IT_REASON
FOR ALL ENTRIES IN _HRP1000
WHERE CANCR = IT_HRP1026-CANCR
AND LANGU = 'E' .
ENDif.
use in this way FOR ALL ENTRIES
<b>Reward if usefull</b>
11-12-2007 7:27 AM
Hi Yonus,
do like this
declare independent internal table and 1 final table for each table and fetch data as below.
select belnr bldat budat monat usnam bktxt waers from bkpf into table it_bkpf where bukrs = p_bukrs and
gjahr = p_gjahr and
blart = p_blart and
budat in s_budat and
monat in s_monat.
if sy-subrc = 0.
if it_bkpf[] is not initial.
select docnr racct wsl drcrk buzei from faglflexa into table it_faglflexa
for all entries in it_bkpf
where docnr = it_bkpf-belnr and
ryear = p_gjahr and
rbukrs = p_bukrs and
rldnr = '0L' and
rvers = '001'.
if sy-subrc = 0.
if it_faglflexa[] is not intial.
select saknr txt50 from skat into table it_skat
for all entries in it_faglflexa
where saknr = it_faglflexa-racct.
if sy-subrc = 0.
select saknr altkt from skb1 into table it_skb1
for all entries in it_skat
where saknr = it_skat-saknr.
endif.
endif.
endif.
endif.
endif.
Once you get all the data to your internal tables, read one by one and move to final internal table.
Hope this will help you.
<b>Reward for helpful answers</b>
Satish
11-12-2007 2:22 PM
1) Rather than using the inner-joins on 4 tables, it would be better to have individual select statements and reconciling
the data later into one single table.
2) Fetch data from BKPF based on the information you have, i.e.
select bukrs gjahr belnr bldat budat monat usnam bktxt waers
from bkpf
into table t_bkpf
where bukrs = p_bukrs
and gjahr = p_gjahr
and blart = p_blart
and budat in s_budat
and monat in s_monat.
3) For all the entries selected above, fetch data from "FAGLFLEXA" based on the document number, fiscal year and company code
in t_bkpf table. Based on the "KEY" in table "FAGLFLEXA" you may fetch more fields from BKPF if required.
IF NOT t_bkpf[] IS INITIAL.
select rbukrs docnr rgjahr buzei racct wsl drcrk
from table faglflexa
into table t_fagl
for all entries in t_bkpf
where rbukrs = t_bkpf-bukrs
and rgjahr = t_bkpf-gjahr
and docnr = t_bkpf-belnr
and rldnr = 'OL'
and rvers = '001'.
ENDIF.
4) Selecting from SKAT. Based on the accounts obtained in t_fagl, we can get the G/L account long text from SKAT. But there
will be mulitple lines with same account in t_fagl. Also, while selecting from SKB1, G/L account and company code are the
details required for fetching the data.
So, may be you can take the data in t_fagl into some other internal table t_fagltemp.
SORT t_fagltemp BY BUKRS RACCT.
DELETE ADJACENT DUPLICATES in t_fagltemp COMPARING FIELDS BUKRS RACCT.
5) IF NOT t_fagltemp[] IS INITIAL.
select spras ktopl saknr
from SKAT
INTO table t_skat
for all entries in t_fagltemp
where saknr = t_fagltemp-racct.
ENDIF.
Here, if the company code i.e. p_bukrs is a parameter and mandatory field, then you can actualll get the language key
i.e. field "SPRAS" from company code master data. And the same can be used in the above query on SKAT.
6) IF NOT t_fagltemp[] IS INITIAL.
select bukrs saknr altkt
FROM TABLE SKB1
into table t_skb1
for all entres in t_fagltemp
where bukrs = t_fagltemp-bukrs
and saknr = t_fagltemp-racct.
ENDIF.
Based on the information obtained above, the data can be formatted according to the requirement into a single internal table.
11-12-2007 2:42 PM
JOINs usually perform better than FOR ALL ENTRIES, contrary to what many believe. Please see:
<a href="/people/rob.burbank/blog/2007/03/19/joins-vs-for-all-entries--which-performs-better">JOINS vs. FOR ALL ENTRIES - Which Performs Better?</a>
and then try:
SELECT a~belnr a~bldat a~budat a~monat a~usnam a~bktxt a~waers
b~racct b~wsl b~drcrk b~buzei c~txt50 d~altkt
INTO TABLE gt_all
FROM bkpf AS a
INNER JOIN faglflexa AS b
ON a~belnr = b~docnr
INNER JOIN skat AS c
ON b~racct = c~saknr
INNER JOIN skb1 AS d
ON d~bukrs = a~bukrs "<== Include company code
AND d~saknr = c~saknr
WHERE a~bukrs = p_bukrs AND
a~bstat = ' ' AND "<== Any others you want too
a~budat IN s_budat AND
a~gjahr = p_gjahr AND
a~blart = p_blart AND
a~monat IN s_monat AND
b~ryear = p_gjahr AND
b~rbukrs = p_bukrs AND
b~rldnr = '0L' AND
b~rvers = '001' AND
c~spras = sy-langu AND "<== Language
c~ktopl = '????'. "<== Chart of accounts
Rob