reg tables.

Former Member — Mon, 07 May 2007 06:41:02 GMT

Hi,

I have the requirement to join two tables and extract data.

BKPF and BSEG(CLUSTER TABLE).

Initially i have to select data from BKPF based on that i have to extract for all the records of BKPF from BSEG.

Since BSEG is a cluster table can anyone suggest me how best i can extract data from any secondary index tables.

Regards,

Ratna

Re: reg tables.

Former Member — Mon, 07 May 2007 06:50:52 GMT

Hi Ratna,

You may use the statement

SELECT XXX, YYY

INTO TABLE IT_BSEG

FROM BSEG

FOR ALL ENTRIES IN TABLE BKPF

WHERE

BSEG-BUKRS = BKPF-BUKRS AND

BSEG-BELNR = BKPF-BELNR AND

BSEG-GJAHR = BKPF-GJAHR.

Re: reg tables.

Former Member — Mon, 07 May 2007 06:56:43 GMT

SELECT  bkpf~lifnr bkpf~bukrs INTO    CORRESPONDING   FIELDS OF  TABLE int_outtab

  FROM (  bkpf

           JOIN bseg ON  bkpf~lifnr = bseg~lifnr )
  WHERE  bkpf~bukrs  =  P_bukrs.

Rewards if helpfull

Regards

Pavan

Re: reg tables.

Former Member — Mon, 07 May 2007 06:57:48 GMT

Hi Ratna,

Use all key fields in where condition as per the order of BSEG table fields.

Some times more secondary index uses is may decrease the performance.

Regards,

Venkat

Re: reg tables.

Former Member — Mon, 07 May 2007 06:59:22 GMT

Hi Ratna,

Check this program,

i hope it is helpful to you.

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.

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.

***************************************************************************

<b>Reward if it is useful to you.</b>

topic Re: reg tables. in Application Development and Automation Discussions

reg tables.

Re: reg tables.

Re: reg tables.

Re: reg tables.

Re: reg tables.