topic ABAP : getting problem in select queries in Application Development and Automation Discussions

ABAP : getting problem in select queries

Former Member — Fri, 14 Dec 2012 05:54:29 GMT

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

Re: ABAP : getting problem in select queries

Former Member — Fri, 14 Dec 2012 06:17:48 GMT

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.

Re: ABAP : getting problem in select queries

Former Member — Fri, 14 Dec 2012 06:31:21 GMT

Dear Mr . Aswatha

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

please suggest some other method.

Regards,

Rihan

Re: ABAP : getting problem in select queries

Former Member — Fri, 14 Dec 2012 06:39:28 GMT

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

Re: ABAP : getting problem in select queries

reachdebopriya — Fri, 14 Dec 2012 06:49:06 GMT

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

Re: ABAP : getting problem in select queries

Former Member — Fri, 14 Dec 2012 07:01:24 GMT

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.

Re: ABAP : getting problem in select queries

Former Member — Fri, 14 Dec 2012 09:22:12 GMT

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

Re: ABAP : getting problem in select queries

former_member194152 — Fri, 14 Dec 2012 09:41:20 GMT

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

Re: ABAP : getting problem in select queries

Former Member — Fri, 14 Dec 2012 09:52:16 GMT

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

Re: ABAP : getting problem in select queries

Former Member — Fri, 14 Dec 2012 10:04:30 GMT

Please close the thread if your question is answered.