topic Re: select statement Issue in Application Development and Automation Discussions

select statement Issue

Former Member — Thu, 13 Jun 2013 10:42:18 GMT

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.

Re: select statement Issue

Former Member — Thu, 13 Jun 2013 11:39:23 GMT

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

Re: select statement Issue

former_member196490 — Thu, 13 Jun 2013 11:39:25 GMT

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.

Re: select statement Issue

RaymondGiuseppi — Thu, 13 Jun 2013 11:57:15 GMT

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

Re: select statement Issue

gurunathkumar_dadamu — Thu, 13 Jun 2013 11:59:04 GMT

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

Re: select statement Issue

TSG — Thu, 13 Jun 2013 13:24:19 GMT

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.

Re: select statement Issue

Former Member — Thu, 13 Jun 2013 13:51:00 GMT

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

Re: select statement Issue

former_member182465 — Fri, 14 Jun 2013 13:22:57 GMT

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.