ABAP : getting problem in select queries

[Former Member]

Dear Friends ,

I am having two Tables to fetch the data (BSEG and BKPF )

I am fetching the data from BSEG  is displayed in ALV grid but the data of BKPF

is not displayed in ALV grid , I am giving here my queries :

DATA : BEGIN OF IBSID OCCURS 0 ,

        VKBUR LIKE KNVV-VKBUR,

        BEZEI LIKE TVKBT-BEZEI,

        BUKRS LIKE BSIK-BUKRS,

        BLART LIKE BKPF-BLART,

        LIFNR LIKE mseg-LIFNR,

        BELNR LIKE BSIK-BELNR,

        SHKZG LIKE BSIK-SHKZG,

        UMSKZ LIKE BSIK-UMSKZ,

        ZUONR LIKE BSIK-ZUONR,

        GJAHR LIKE BSIK-GJAHR,

        BUZEI LIKE BSIK-BUZEI,

        BUDAT LIKE BSIK-BUDAT,

        BLDAT LIKE BSIK-BLDAT,

        FKLMG1 LIKE S900-FKLMG,

        DMBTR    LIKE BSIK-DMBTR,

        AGE30    LIKE BSIK-DMBTR,

        AGE60    LIKE BSIK-DMBTR,

        AGE180   LIKE BSIK-DMBTR,

        AGE360   LIKE BSIK-DMBTR,

        AGE360M  LIKE BSIK-DMBTR,

        AGEMR    LIKE BSIK-DMBTR,

        AGEMRAB  LIKE  BSIK-DMBTR,

        DMBTRCR  LIKE BSIK-DMBTR,

        DMBTRDR  LIKE BSIK-DMBTR,

        AGE30C   LIKE BSIK-DMBTR,

        AGE60C   LIKE BSIK-DMBTR,

        AGE180C  LIKE BSIK-DMBTR,

        AGE360C  LIKE BSIK-DMBTR,

        AGE360MC LIKE BSIK-DMBTR,

        AGEMRC   LIKE BSIK-DMBTR,

        AGEMRABC LIKE  BSIK-DMBTR,

        TOTAL    LIKE BSIK-DMBTR,

        GSBER    LIKE BSIK-GSBER,

*        KKBER    LIKE BSIK-KKBER,

        ZBD1T(5) TYPE C,

        NODAYS TYPE I,

        HKONT LIKE BSIK-HKONT,

        VALUT like bseg-VALUT,

        ebeln like bseg-ebeln,

        AUGDT like bseg-AUGDT,

        augbl like bseg-augbl,

      END OF IBSID.

DATA: IBSIG LIKE IBSID OCCURS 0 WITH HEADER LINE.

  SELECT BELNR BUDAT BLART FROM bkpf INTO corresponding fields of

  table  ibsig

                    WHERE bukrs = '1100'

                      AND BUDAT  IN  S_BUDAT

                      AND   gjahr IN s_gjahr

*                      AND ( blart EQ 'RE' OR  blart EQ 'WE' OR blart EQ 'WA')

                      AND  blart in ('RE','WE','WA')

                      AND tcode IN ('MIR7','FBCJ','FB60','FB08', 'MIRO', 'MIGO_GR')

                      AND stblg EQ space

                      %_HINTS ORACLE 'index(BKPF"ZNB")'.

LOOP AT IBSIG.

select  belnr  GJAHR EBELN DMBTR SHKZG AUGDT AUGBL LIFNR HKONT

from bseg INTO CORRESPONDING FIELDS OF TABLE IBSID

FOR ALL ENTRIES IN IBSIG  where

                belnr eq ibsig-belnr

                and hkont in s_hkont

                and gjahr in s_gjahr

                and lifnr in s_lifnr.

move ibsig-budat to ibsid-budat.

move ibsig-blart to ibsid-blart.

DELETE IBSID WHERE BUDAT LT S_BUDAT-low and budat Gt s_budat-high.

DELETE IBSID WHERE AUGDT NE 0 .

DELETE IBSID WHERE BELNR EQ 0 .

ENDLOOP.

loop at ibsid.

IF IBSId-SHKZG EQ 'H' AND IBSId-DMBTR > 0.

        IBSID-DMBTR =  IBSId-DMBTR * -1.

  ENDIF.

modify ibsid .

endloop.

where I am making mistake

Please suggest .

Regards,

Rihan

[Former Member]

Hi,

Not a good approach this using select inside loop.

since you have only two tables why dont you join them and select all the necessary fields into your final internal table.

thanks.

[Former Member]

Dear Mr . Aswatha

Bseg is a cluster table so we can not use Join condition for that .

please suggest some other method.

Regards,

Rihan

[Former Member]

Hi,

Get all the data in internal table first then work on the internal table same as you are working on normal db operation.

hope this helps.

Avirat

[Former Member]

Select all the data from one table into an internal table say itab1.

select all the data from second table into another internal table say itab2 for all entries of table itab1 on belnr and bjahr.

now loop at itab1.

read table itab2 with key itab1 belnr bjahr

now using these two work area data you can add data to final internal table which you use to display in alv.

thanks.

[reachdebopriya]

Hi Rihan,

First create internal table for BKPF and fetch data from BKPF and put it in the internal table.

Second if the requirement is for Customer fetch data from BSID and BSAD or if the requirement is for Vendor fetch data from BSIK and BSAK using for all entries with the internal table of BKPF.

Try to fetch data index wish. Performance will be better.

Then create the final internal table. Loop at _BKPF and in read data of the internal table of BSID and BSAD or BSIK and BSAK and populate the final internal table.

Note: BSID and BSAD table structure is same. so no need to create separate internal table for BSID and BSAD. Same for BSIK and BSAK.

Example:

*-- Table BKPF

SELECT bukrs    "Company Code
         belnr    "Accounting Document Number
         gjahr    "Fiscal Year
         blart    "Document Type
         bstat    "Document Status
  FROM bkpf
  INTO TABLE t_bkpf
  WHERE bukrs EQ p_bukrs
      AND blart IN s_blart.

*-- Table BSIK
    SELECT bukrs "Company Code
           lifnr "Account Number of Vendor or Creditor
           umsks "Special G/L Transaction Type
           umskz "Special G/L Indicator
           augdt "Clearing Date
           augbl "Document Number of the Clearing Document
           zuonr "Assignment Number
           gjahr "Fiscal Year
           belnr "Accounting Document Number
           buzei "Number of Line Item Within Accounting Document
           bldat "Document Date in Document
           waers "Currency Key
           xblnr "Reference Document Number
           blart "Document Type
           dmbtr "Amount in Local Currency
           hkont "General Ledger Account
      FROM bsik
      INTO TABLE t_bsik
      FOR ALL ENTRIES IN fp_gt_bkpf
      WHERE lifnr IN s_lifnr
    AND bukrs = fp_gt_bkpf-bukrs
    AND gjahr = fp_gt_bkpf-gjahr
    AND belnr = fp_gt_bkpf-belnr
    AND buzei GT lc_0.

*-- Table BSAK

    SELECT bukrs "Company Code
           lifnr "Account Number of Vendor or Creditor
           umsks "Special G/L Transaction Type
           umskz "Special G/L Indicator
           augdt "Clearing Date
           augbl "Document Number of the Clearing Document
           zuonr "Assignment Number
           gjahr "Fiscal Year
           belnr "Accounting Document Number
           buzei "Number of Line Item Within Accounting Document
           bldat "Document Date in Document
           waers "Currency Key
           xblnr "Reference Document Number
           blart "Document Type
           dmbtr "Amount in Local Currency
           hkont "General Ledger Account
      FROM bsak
      APPENDING TABLE t_bsik
      FOR ALL ENTRIES IN fp_gt_bkpf
      WHERE lifnr IN s_lifnr
    AND bukrs = fp_gt_bkpf-bukrs
    AND gjahr = fp_gt_bkpf-gjahr
    AND belnr = fp_gt_bkpf-belnr
    AND buzei GT lc_0.

Thanks,

Debopriya Ghosh

[Former Member]

Dear Friends ,

i have applied all of your methods but my query is not related to performance tuning or

how to select the data .My query is that the data of BSEG is displayed by the data of

BKPF not displayed and I have applied all of your method but still I am getting the same

Problem.

Kindly suggest.

rihan

[former_member194152]

Hi,

You are not modifying internal table ibsid, in LOOP AT IBSIG. ENDLOOP segment.

after statement :

move ibsig-budat to ibsid-budat.
move ibsig-blart to ibsid-blart.

is correct but again you need to loop ibsid table and then modify bkpf related entries in it.

Same thing you can do it in loop segment where you are changing dmbtr field from negative to positive.

Thanks,

Gagan

[Former Member]

Dear all I have got the solution of above problem .

It is that I understand that If I am using for all entries then all entries of first internal table

will come to second internal table but it is not like that , To get the values of first internal

table into another I have to use read command like that ,

SELECT * FROM BKPF  INTO CORRESPONDING FIELDS OF TABLE IBSIG

                    WHERE bukrs = '1100'

                      AND BUDAT  IN  S_BUDAT

                      AND   gjahr IN s_gjahr

                      AND  blart in ('RE','WE','WA')

                      AND tcode IN ('MIR7','FBCJ','FB60','FB08', 'MIRO', 'MIGO_GR')

                      AND stblg EQ space

                      %_HINTS ORACLE 'index(BKPF"ZNB")'.

IF NOT IBSIG[] IS INITIAL.

select  belnr  GJAHR EBELN DMBTR SHKZG AUGDT AUGBL LIFNR HKONT

from bseg INTO CORRESPONDING FIELDS OF TABLE IBSID FOR ALL ENTRIES IN IBSIG

          where belnr eq ibsig-belnr

                and hkont in s_hkont

                and gjahr in s_gjahr

                and lifnr in s_lifnr.

ENDIF.

" now I have to take the values of IBSIG internal table into IBSID internal table

then I have to do the following procedure  and I have to use READ statement  :

SORT IBSID BY BELNR .

LOOP AT IBSID .

READ TABLE IBSIG WITH KEY BELNR = IBSID-BELNR.

move ibsig-budat to ibsid-budat.

move ibsig-blart to ibsid-blart.

DELETE IBSID WHERE BUDAT LT S_BUDAT-low and budat Gt s_budat-high.

DELETE IBSID WHERE AUGDT NE 0 .

DELETE IBSID WHERE BELNR EQ 0 .

IF IBSId-SHKZG EQ 'H' AND IBSID-DMBTR > 0.

        IBSID-DMBTR =  IBSId-DMBTR * -1.

ENDIF.

MODIFY IBSID.

ENDLOOP.

now I have got the solution .

Thanks all for your kind support,

regards,

Rihan

[Former Member]

Please close the thread if your question is answered.