Open SQL with Custom fields in Standard table

[Former Member]

Dear Experts,

I am facing a problem in ABAP Open SQL with custom field in Standard table.

I have added two custom fields in standard table. like below:

Standard table : AUFM (Goods Movement for order)

Custom fields:

ZZID       TYPE      MB_LINE_ID

ZZPID      TYPE      MB_PARENT_ID

When I trigger the open SQL on this table, first time it give me wrong data in custom fields, and after that or immediately I trigger the same sql, it give me correct data in custom fields.

I don't know what is the problem with custom fields.

Please help.

Thanks

[Former Member]

Can you please elaborate on what query you fire on this table.

[Former Member]

SQL is as below:

data: begin of lw_aufm,

        mblnr type aufm-mblnr,

        mjahr type aufm-mjahr,

        zeile type aufm-zeile,

        budat type aufm-budat,

        werks type aufm-werks,

        matnr type aufm-matnr,

        charg type aufm-charg,

        lgort type aufm-lgort,

        bwart type aufm-bwart,

        aufnr type aufm-aufnr,

        menge type aufm-menge,

        arbpl type arbpl,

        dauat type afpo-dauat,

        zzid  type aufm-zzid,

        zzpid type aufm-zzpid,

      end of lw_aufm,

      i_aufm like standard table of lw_aufm.

select aufm~mblnr aufm~mjahr aufm~zeile aufm~budat aufm~werks aufm~matnr

          aufm~charg aufm~lgort aufm~bwart aufm~aufnr aufm~menge

          crhd~arbpl afpo~dauat aufm~zzid aufm~zzpid

     into table i_aufm

     from aufm

       inner join afpo on aufm~aufnr = afpo~aufnr

       inner join afvc on aufm~aufpl = afvc~aufpl

                      and aufm~aplzl = afvc~aplzl

       inner join crhd on afvc~arbid = crhd~objid

     where aufm~mjahr in s_mjahr

       and aufm~budat in s_budat

       and aufm~bwart in ('101', '261', '531')

       and afpo~dauat in s_auart

       and crhd~arbpl in s_arbpl.

First time this sql return wrong values in custom fields, but second time it returns correct values.

[Former Member]

Dear Experts,

Pl. help me in this problem. how can I solve this problem.

Is it a SAP bug or any SAP profile parameters configuration mistake.

[former_member183607]

Hi,

     Declare used tables in top of program after Report Statement.

     Tables : AUFM,AFPO,AFVC,CRHD.

     Use By passing buffer clause in select statement.

Regards

[Former Member]

Dear Somendra,

Thanks for reply,

Previously My SQL is working perfectly, but after addition of the custom fields, it giving me wrong data.

As per your reply, this is a tricky solution, but what is the exact reason.

Regards,

[Former Member]

Hi

What do you mean with wrong data?

Max

[Former Member]

Hi,

Wrong data means,

The SQL returns same number of records all time and gives correct values in standard fields, but in the custom field, it returns wrong data.

like: if AUFM table has value in database for field

ZZID = 2 and ZZPID = 5,  "This is the correct value in database table. when we browse the data in SE11/SE16

but the first execution of SQL returns the wrong value in custom fields like:

ZZID = 5 and ZZPID = 7. "First execution of the SQL return the wrong value, checked in Debugger/Report output.

And second execution of same SQL with same where clause returns the correct data like:

ZZID = 2 and ZZPID = 5. "Second execution of the same SQL return the correct value, checked in Debugger/Report output.

SQL always return wrong values in custom fields at first time execution and after second execution(after 1 or more seconds) of the same SQL return correct data.

For the Temporary solution, I have executed the same SQL twice in the code like below.

data: begin of lw_aufm,

        mblnr type aufm-mblnr,

        mjahr type aufm-mjahr,

        zeile type aufm-zeile,

        budat type aufm-budat,

        werks type aufm-werks,

        matnr type aufm-matnr,

        charg type aufm-charg,

        lgort type aufm-lgort,

        bwart type aufm-bwart,

        aufnr type aufm-aufnr,

        menge type aufm-menge,

        arbpl type arbpl,

        dauat type afpo-dauat,

        zzid  type aufm-zzid,

        zzpid type aufm-zzpid,

      end of lw_aufm,

      i_aufm like standard table of lw_aufm.

select aufm~mblnr aufm~mjahr aufm~zeile aufm~budat aufm~werks aufm~matnr

          aufm~charg aufm~lgort aufm~bwart aufm~aufnr aufm~menge

          crhd~arbpl afpo~dauat aufm~zzid aufm~zzpid

     into table i_aufm

     from aufm

       inner join afpo on aufm~aufnr = afpo~aufnr

       inner join afvc on aufm~aufpl = afvc~aufpl

                      and aufm~aplzl = afvc~aplzl

       inner join crhd on afvc~arbid = crhd~objid

     where aufm~mjahr in s_mjahr

       and aufm~budat in s_budat

       and aufm~bwart in ('101', '261', '531')

       and afpo~dauat in s_auart

       and crhd~arbpl in s_arbpl.

**** Here SQL return wrong values in custom fields only.

if sy-subrc = 0.

   select aufm~mblnr aufm~mjahr aufm~zeile aufm~budat aufm~werks aufm~matnr

             aufm~charg aufm~lgort aufm~bwart aufm~aufnr aufm~menge

             crhd~arbpl afpo~dauat aufm~zzid aufm~zzpid

        into table i_aufm

        from aufm

          inner join afpo on aufm~aufnr = afpo~aufnr

          inner join afvc on aufm~aufpl = afvc~aufpl

                         and aufm~aplzl = afvc~aplzl

          inner join crhd on afvc~arbid = crhd~objid

        where aufm~mjahr in s_mjahr

          and aufm~budat in s_budat

          and aufm~bwart in ('101', '261', '531')

          and afpo~dauat in s_auart

          and crhd~arbpl in s_arbpl.

**** Here SQL return correct values in custom fields only.

endif.

Regards,

[Former Member]

Hi

Have you tried to check the database definition?

And how do you update your z-fields?

Max

[Former Member]

Every time you execute the program a fresh, Does it bring the same wrong values first time or they are random?

[Former Member]

Hi Max,

Database updates work perfectly, becoz database table shows correct values in browser.

Which type of database definition?

This is a Standard table and Definition, if you are asking about Technical definition,

Regards,

[Former Member]

Dear Abhijit,

If I login in the system first time, and run the report, it shows me wrong data and after that, I just press Back and execute the report, it shows correct data for the same parameters.

If I change the parameters value(Like date) at every execution, report shows wrong data every time.

After the first execution, all next executions shows correct data for same parameters.

If I logged out and login after 4-5 hours and run the same report with same parameters, the report again shows me wrong data.

Take an example:

>>>Login.

1. I run the report with date 10.08.2014 = Return wrong value.

2. I run the report with date 10-08.2014 = Return correct value.

3. I run the report with date 15.08.2014 = Return wrong value.

4. I run the report with date 16.08.2014 = Return wrong value.

5. I run the report with date 15.08.2014 = Return Correct value.

>>> Log out and Login after 4-5 hours.

6. I run the report with date 10.08.2014 = Return wrong value.

7. I run the report with date 10.08.2014 = Return correct value.

Regards.

[Former Member]

Hi

I mean database definition, not dictionary definition: yuo can see the database definition in database utilities (trx SE14)

Max

[Private_Member_7726]

Hi,

What have you done to analyze the query and isolate the problem..? Frankly, at the moment I have hard time believing any information you are giving, due to the woefully inadequate information given in the first post and the amount of incomplete or irrelevant information given later... Let's give it one try...

1) what database is this?

2) can you reproduce the behavior claimed via SE16N using a corresponding DDIC View?

3) extract using SE16N and attach representative sample of test data (per table) you are using and both the incorrect and correct result sets for date 10.08.2014, please

Please create a program containing nothing but the data definitions, select options, a query and verify via debugger:

4) if anything changes with respect to zz fields in the result set when you change the order of columns in result specification (all AUFM columns first for example) and use INTO CORRESPONDING FIELDS OF TABLE?

5) anything changes when you remove AFPO from the query?

6) anything changes when you remove CRHD from the query?

7) anything changes when you remove AFVC and CRHD from the query?

cheers

Jānis

[Former Member]

Dear Janis,

1,Database is Oracle.

2.I have access aufm table/Database View via se16, and the browser shows correct data for date 10.08.2014.

3. And I don't want to use INTO CORRESPONDING FIELDS(Performance Issue).

[Private_Member_7726]

It's a myth INTO CORRESPONDING addition would result in measurable performance penalty, I believe, but other than that... I think I've read enough... It is highly unlikely anybody here will be able to help you if you are unwilling to perform some steps to try to isolate the problem (or come up with your own steps to do so)...

take care

Jānis

[Former Member]

Hi,

Try INTO CORRESPONDING FIELDS OF TABLE i_aufm

Regards

[Former Member]

Hi Ruks,

I don't want to use INTO CORRESPONDING FIELDS (Performance Issue).

Regards,

[VenkatRamesh_V]

Hi Narendra,

Try this,

Create Secondary Index for the NON Key Fields used in AUFM table,

Include the Secondary Index in Select query and check it.

Regards,

Venkat.

[chaouki_akir]

if you access to AUFM using SE16, what is the behaviour ? same as your report ?

You can create a much more simpler program that will access to aufm entries. And check the behaviour.

You can also using SQVI transaction try to create a query that will join tables AUFM/AFPO/AFVC/CRHD. And check the result.

[Former Member]

Dear All,

My program was working perfectly before(Around 6 years) adding the custom fields.

Why the program behaving like this after addition of custom fields.

No changes in program has done except Custom fields addition in ITAB, SQL and Loop processing.

And why it working correctly at second execution.

Regards,

[Former Member]

If you remove the ZZ fields does your program behave correctly? If you are saying nothing has changed in the program except the addition of these fields to Internal table SQL and LOOP then it is going to be challenging..

Regards