select statement Issue

[Former Member]

Hi friends.

I am fetching data from BSIS & BSEG CODE IS AS FOLLOWS.

REFRESH it_bsis[].
   SELECT bukrs    "COMPANY CODE
          hkont    "GENERAL LEDGER ACCOUNT
          gjahr    "FISCAL YEAR
          belnr    "ACCOUNTING DOCUMENT NUMBER
          waers    "CURRENCY KEY
          monat    "FISCAL PERIOD
          shkzg    "Debit/Credit Indicator
          dmbtr    "AMOUNT IN LOCAL CURRENCY
          wrbtr    "Amount in Document Currency
     FROM bsis
     INTO TABLE it_bsis
     WHERE bukrs IN s_bukrs
       AND hkont IN s_hkont.


   IF sy-subrc EQ 0.
     SORT it_bsis BY  bukrs  hkont gjahr monat belnr.
     DELETE it_bsis WHERE monat NE p_monat
                     AND  monat NE g_previous_month.

     DELETE it_bsis WHERE gjahr NE p_gjahr
                     AND  gjahr NE g_previous_year.
   ENDIF.

IF it_bsis IS NOT INITIAL.
     REFRESH it_bseg[].
     SORT it_bseg BY bukrs belnr gjahr hkont.
     SELECT bukrs
            belnr
            gjahr
            shkzg
            wrbtr
            pswsl
            hkont
       FROM bseg
       INTO TABLE it_bseg
       FOR ALL ENTRIES IN it_bsis
       WHERE   bukrs IN s_bukrs
               AND belnr EQ it_bsis-belnr
               AND gjahr EQ p_gjahr
               AND hkont IN s_hkont.

ENDIF.

selection screen :

o/p :

in debugg Mode when i see,

in It_bsis 868 records,

in it_bseg 867 records are comming.

but when i give the same selection creteria in se11.

in bsis 868 records

in bseg 867 records are comming.

here finally one record is missing.

can any one tell me the solution.

i am attaching the file which record is missing in red color.

data is white color are se11 records & data in Yellow are from debugg mode records.

Plz can any one can help me the solution.

[Former Member]

Hi Dhana,

a SELECT for all entries is a SELECT DISTINCT with automatically deleting duplicates.

In BSIS and BSEG you have a Field BUZEI.

Please change your SELECT FROM BSEG to get field BUZEI additionally.

Regards,

Klaus

[former_member196490]

Hi,

The it_bsis would contain entries where gjahr = g_previous_year but it_bseg would not. This could be a possible reason for the difference.

[RaymondGiuseppi]

As a rule of thumb, when you cannot avoid a FOR ALL ENTRIES, always select the whole primary keys of read tables to prevent deletion of false duplicate records.

Also check the deletion rules on period/year, could you try to put those in first selection of data ?

Regards,

Raymond

[gurunathkumar_dadamu]

Hi Dhana radhi,

Try include  current mont and year as well as previous moht and year in select satement itself.                                            gjahr eq p_gjahr

                               and  gjahr  eq g_previous_year

                              and   monat eq p_monat

                              and   monat  eq g_previous_month.

let me know if any issues.

Regards,

Guru

[TSG]

Hi Dhana,

when you join BSEG AND BSIS use BUZEI filed also.

first add BUZEI filed your internal table it_bsis.

then your join should be below ways.

IF it_bsis IS NOT INITIAL.
     REFRESH it_bseg[].
     SORT it_bseg BY bukrs belnr gjahr hkont.
     SELECT bukrs
            belnr
            gjahr
            shkzg
            wrbtr
            pswsl
            hkont
       FROM bseg
       INTO TABLE it_bseg
       FOR ALL ENTRIES IN it_bsis
       WHERE   bukrs eq it_bsis-bukrs
               AND belnr EQ it_bsis-belnr
               AND gjahr EQ it_bsis-gjahr
               AND hkont eq it_bsis-hkont

               and  BUZEI eq it_bsis-BUZEI.

ENDIF..

please try above.

[Former Member]

Hi,

FOR ALL ENTRIES will not fetch duplicates. So it will fetch only first BELNR regardless of how many line items it is having.

Adding BUZEI w.r.t BELNR in your SELECT Query field will solve your issue.

Arun

[former_member182465]

Hi Dhana,

Raymond and Arun comments are correct. Selecting all primary key fields is the important point to be considered while using For all entries.