08-07-2007 11:57 AM
How to join the cluster table with the transparent table?In specific ,can you pls tell me how can i join bkpf and bseg?
08-07-2007 12:05 PM
HI Aravind,
You can not Join on Cluster or Pool tables.
Use FOR ALL ENTRIES instead of JOINS.
Reward points if helpful and close the thread it is solved/answered.
Thanks,
Vinay
08-07-2007 12:03 PM
Hi,
We cant perform joins on cluster tables, so first get the data from cluster table(BSEG) into internal table and use SELECT..with FOR ALL ENTRIES option on transparent table(BKPF).
SELECT * FROM BSEG
INTO IT_BSEG
WHERE <CONDITION>.
IF IT_BSEG[] IS NOT INITIAL.
SELECT <F1> <F2> FROM BKPF
INTO IT_FINAL
FOR ALL ENTRIES IN IT_BSEG
WHERE <CONDITON>.
ENDIF.
Regards,
Priyanka
08-08-2007 11:53 AM
hi,
Thank u for your reply,provided infromatoin is useful for me.....
Regards,
Aravind
08-07-2007 12:05 PM
HI Aravind,
You can not Join on Cluster or Pool tables.
Use FOR ALL ENTRIES instead of JOINS.
Reward points if helpful and close the thread it is solved/answered.
Thanks,
Vinay
08-07-2007 12:08 PM
Hi,
Cluster tables are defined in the data dictionary as transparent tables.Retrieval of data is very fast if the primary key is known. so you need to use all the primary keys in the where condition, You can join the tables, but it is better to get the data into a Internal table then use the FOR ALL ENTRIES
Regards
Sudheer
08-07-2007 12:16 PM
Hi
Joining in cluster table is not possible ,since cluster table itself is made from many tables.
instead of that , Use FOR ALL ENTRIES OPTION
08-07-2007 12:21 PM
Hi Aravind,
Check this code,
tables : bkpf,
bseg.
************************************************************************
INTERNAL TABLE AND WORK AREA FOR THE FIELDS IN BKPF TABLE *
************************************************************************
data : begin of itab_bkpf occurs 0,
bukrs like bkpf-bukrs, "Company Code.
gjahr like bkpf-gjahr, "Fiscal Year.
budat like bkpf-budat, "Posting Date in the Document.
belnr like bkpf-belnr, "Accounting document number.
blart like bkpf-blart, "Document Type.
end of itab_bkpf.
data : wa_bkpf like line of itab_bkpf.
************************************************************************
INTERNAL TABLE AND WORK AREA FOR THE FIEDLS IN BSEG TABLE *
************************************************************************
data : begin of itab_bseg_debit occurs 0,
bukrs like bseg-bukrs, "Company Code.
gjahr like bseg-gjahr, "Fiscal Year.
belnr like bseg-belnr, "Accounting Document Number.
buzei like bseg-buzei, "Line Item.
hkont like bseg-hkont, "General Leadger Account.
shkzg like bseg-shkzg, "Credit/Debit Indicator.
wrbtr like bseg-wrbtr, "Amount in Document Currency.
pswsl like bseg-pswsl, "Update Currency for Gen.Ledger
dmbtr like bseg-dmbtr, "Amount in local currency.
sgtxt like bseg-sgtxt, "Item Text.
zuonr like bseg-zuonr, "Assignment Number.
end of itab_bseg_debit.
data : itab_bseg_credit like standard table of itab_bseg_debit with
header line.
************************************************************************
FINAL OUTPUT INTERNAL TABLE *
************************************************************************
data : begin of itab_output occurs 0,
belnr(08) ,
bukrs(04) ,
budat like bkpf-budat,
buzei(03) ,
hkont(07) ,
blart(02) ,
shkzg(01) ,
wrbtr(08) ,
pswsl(05) ,
dmbtr(10) ,
sgtxt(19) ,
zuonr(10) ,
end of itab_output.
************************************************************************
constants : c_debit type c value 'S',
c_credit type c value 'H'.
************************************************************************
SELECT-OPTIONS *
************************************************************************
selection-screen begin of block input with frame title text-t01.
select-options : s_bukrs for bkpf-bukrs.
parameters : p_year like bkpf-gjahr visible length 2.
select-options : s_budat for bkpf-budat,
s_dbacct for bseg-hkont,
s_cracct for bseg-hkont,
s_amt for bseg-dmbtr.
selection-screen end of block input.
************************************************************************
SELECTING RECORDS FROM BKPF TABLE BASED ON THE CONDITION *
************************************************************************
select bukrs gjahr budat belnr blart
from bkpf into table itab_bkpf
where bukrs in s_bukrs and
gjahr eq p_year and
budat in s_budat.
************************************************************************
SELECTING DEBIT LINE ITEMITEMS FROM BSEG FOR THE DOCUMENT *
NUMBER SELECTED FROM BKPF *
************************************************************************
if not itab_bkpf[] is initial.
select bukrs gjahr belnr buzei
hkont shkzg wrbtr pswsl
dmbtr sgtxt zuonr
from bseg into table itab_bseg_debit
for all entries in itab_bkpf
where bukrs eq itab_bkpf-bukrs and
belnr eq itab_bkpf-belnr and
gjahr eq itab_bkpf-gjahr and
hkont in s_dbacct and
shkzg eq c_debit and
dmbtr in s_amt.
************************************************************************
SELECTING CREDIT LINE ITEMITEMS FROM BSEG FOR THE DOCUMENT *
NUMBER SELECTED FROM BKPF *
************************************************************************
select bukrs gjahr belnr buzei
hkont shkzg wrbtr pswsl
dmbtr sgtxt zuonr
from bseg into table itab_bseg_credit
for all entries in itab_bkpf
where bukrs eq itab_bkpf-bukrs and
belnr eq itab_bkpf-belnr and
gjahr eq itab_bkpf-gjahr and
hkont in s_cracct and
shkzg eq c_credit and
dmbtr in s_amt.
endif.
sort itab_bkpf by bukrs gjahr belnr.
sort itab_bseg_credit by bukrs gjahr belnr.
************************************************************************
LOOPING THE DEBIT ENTRIES *
************************************************************************
loop at itab_bseg_debit.
************************************************************************
READING THE CREDIT ENTRIES WHICH MATCHES WITH HE CURRENT DOC. NUMBER *
************************************************************************
read table itab_bseg_credit with key
bukrs = itab_bseg_debit-bukrs
gjahr = itab_bseg_debit-gjahr
belnr = itab_bseg_debit-belnr binary search.
if sy-subrc eq 0.
************************************************************************
*READING THE POSTING DATE AND DOCUMENT TYPE FOR THE CURRENT DOUCMENT *
AND APPENDING THE DEBIT AND CREDIT ENTRIES *
************************************************************************
read table itab_bkpf into wa_bkpf with key
bukrs = itab_bseg_debit-bukrs
gjahr = itab_bseg_debit-gjahr
belnr = itab_bseg_debit-belnr binary search.
itab_output-belnr = itab_bseg_debit-belnr.
itab_output-bukrs = itab_bseg_debit-bukrs.
itab_output-budat = wa_bkpf-budat.
itab_output-buzei = itab_bseg_debit-buzei.
itab_output-hkont = itab_bseg_debit-hkont.
itab_output-blart = wa_bkpf-blart.
itab_output-shkzg = itab_bseg_debit-shkzg.
itab_output-wrbtr = itab_bseg_debit-wrbtr.
itab_output-pswsl = itab_bseg_debit-pswsl.
itab_output-dmbtr = itab_bseg_debit-dmbtr.
itab_output-sgtxt = itab_bseg_debit-sgtxt.
itab_output-zuonr = itab_bseg_debit-zuonr.
append itab_output.
itab_output-belnr = itab_bseg_credit-belnr.
itab_output-bukrs = itab_bseg_credit-bukrs.
itab_output-budat = wa_bkpf-budat.
itab_output-buzei = itab_bseg_credit-buzei.
itab_output-hkont = itab_bseg_credit-hkont.
itab_output-blart = wa_bkpf-blart.
itab_output-shkzg = itab_bseg_credit-shkzg.
itab_output-wrbtr = itab_bseg_credit-wrbtr.
itab_output-pswsl = itab_bseg_credit-pswsl.
itab_output-dmbtr = itab_bseg_credit-dmbtr.
itab_output-sgtxt = itab_bseg_credit-sgtxt.
itab_output-zuonr = itab_bseg_credit-zuonr.
append itab_output.
endif.
endloop.
sort itab_output by belnr budat shkzg.
************************************************************************
LOOPING OUTPUT INTERNAL TABLE *
************************************************************************
*FORMAT INTENSIFIED INPUT.
*FORMAT INVERSE ON.
loop at itab_output.
format hotspot on.
format color 5 inverse.
write 😕 sy-vline,
000 itab_output-belnr, 12 sy-vline,
013 itab_output-bukrs, 17 sy-vline,
019 itab_output-budat, sy-vline,
034 itab_output-buzei, 40 sy-vline,
042 itab_output-hkont, sy-vline,
054 itab_output-blart, 60 sy-vline,
065 itab_output-shkzg, 70 sy-vline,
072 itab_output-wrbtr, sy-vline,
085 itab_output-pswsl, sy-vline,
093 itab_output-dmbtr, sy-vline,
106 itab_output-sgtxt, sy-vline,
itab_output-zuonr, sy-vline.
format hotspot off.
endloop.
uline 0(139).
<b>Regards,
Jackie.</b>