topic Open SQL with Custom fields in Standard table in Application Development and Automation Discussions

Open SQL with Custom fields in Standard table

Former Member — Thu, 14 Aug 2014 05:51:44 GMT

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

Re: Open SQL with Custom fields in Standard table

Former Member — Thu, 14 Aug 2014 06:34:32 GMT

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

Re: Open SQL with Custom fields in Standard table

Former Member — Thu, 14 Aug 2014 07:02:39 GMT

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.

Re: Open SQL with Custom fields in Standard table

Former Member — Fri, 22 Aug 2014 09:53:09 GMT

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.

Re: Open SQL with Custom fields in Standard table

Former Member — Fri, 22 Aug 2014 10:03:21 GMT

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

Re: Open SQL with Custom fields in Standard table

Former Member — Fri, 22 Aug 2014 10:10:16 GMT

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,

Re: Open SQL with Custom fields in Standard table

Former Member — Fri, 22 Aug 2014 10:14:26 GMT

What do you mean with wrong data?

Max

Re: Open SQL with Custom fields in Standard table

Former Member — Fri, 22 Aug 2014 10:36:44 GMT

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,

Re: Open SQL with Custom fields in Standard table

Former Member — Fri, 22 Aug 2014 12:05:00 GMT

Have you tried to check the database definition?

And how do you update your z-fields?

Max

Re: Open SQL with Custom fields in Standard table

Former Member — Fri, 22 Aug 2014 12:11:58 GMT

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

Re: Open SQL with Custom fields in Standard table

Former Member — Fri, 22 Aug 2014 18:13:35 GMT

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,

Re: Open SQL with Custom fields in Standard table

Former Member — Fri, 22 Aug 2014 18:24:33 GMT

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.

Re: Open SQL with Custom fields in Standard table

Former Member — Fri, 22 Aug 2014 18:45:22 GMT

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

Max

Re: Open SQL with Custom fields in Standard table

Former Member — Sat, 23 Aug 2014 03:40:19 GMT

Hi,

Try INTO CORRESPONDING FIELDS OF TABLE i_aufm

Regards

Re: Open SQL with Custom fields in Standard table

VenkatRamesh_V — Sat, 23 Aug 2014 04:21:46 GMT

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.

Re: Open SQL with Custom fields in Standard table

Private_Member_7726 — Sat, 23 Aug 2014 11:42:18 GMT

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

Re: Open SQL with Custom fields in Standard table

chaouki_akir — Sun, 24 Aug 2014 20:23:39 GMT

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.

Re: Open SQL with Custom fields in Standard table

Former Member — Mon, 25 Aug 2014 05:07:16 GMT

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).

Re: Open SQL with Custom fields in Standard table

Former Member — Mon, 25 Aug 2014 05:08:23 GMT

Hi Ruks,

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

Regards,

Re: Open SQL with Custom fields in Standard table

Former Member — Mon, 25 Aug 2014 05:13:19 GMT

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,