FOR ALL ENTRIES not working?

[Former Member]

Hi.

I have the following Query:--

• internal table to get the INOB values

Data: begin of lit_inob occurs 0,

cuobj like inob-cuobj,

objek like inob-objek,

end of lit_inob.

• internal table to get the AUSP values

Data: begin of lit_ausp occurs 0,

atwrt like ausp-atwrt,

end of lit_ausp.

• internal table for data type conversion

Data: begin of lit_inob1 occurs 0,

objek like ausp-objek, "for AUSP

end of lit_inob1.

Select cuobj "OBJECT KEY

objek "MATERIAL NUMBER

from inob into table lit_inob

where objek = '000000000013222869' and klart = 'Z01'.

if sy-subrc = 0.

***to convert data type.

Loop at lit_inob.

move lit_inob-cuobj to lit_inob1-objek.

append lit_inob1.

clear lit_inob1.

Endloop.

select atwrt "Characteristic value

into table lit_ausp from ausp

for all entries in lit_inob1

where objek = lit_inob1-objek

and atinn = 'C02000' "used for material category

and klart = 'Z01'

and atwrt ne '00001'.

endif.

The sy-subrc should be 0. but its not in my case.

I couldnt understand where I am wrong.

Kindly provide me with solution.

Thanks

[Former Member]

Subash,

Are you sure for the given where clause you have entries in the table?

Regards,

Ravi

[Former Member]

Hi Ravi.

Yes I am sure that the table has got value.

Otherwise Is there anything wrong in the coding?

Thanks

[Former Member]

Hi Subash,

If you are using FOR ALL ENTRIES u will need to use all the key fields. FOR ALL ENTRIES does not work always if the keys are not specified completely.

Cheers

VJ

[Former Member]

Take a look at the OSS NOTE 65554

Symptom

A SELECT ... FOR ALL ENTRIES delivers duplicate lines or lines are missing in the results.

Other terms

FOR_ALL_ENTRIES, FOR-ALL-ENTRIES, CORRESPONDING FIELDS

Reason and Prerequisites

1. Not all primary key fields are contained in the SELECT list, but one or several primary key fields appear in duplicate as a result. If you can now use a statement to execute the SELECT command on the database, the process of eliminating the duplicate lines may sometimes be skipped.

If several statements appear on the database, non-identical lines may also be removed when the duplicate lines are eliminated.

2. Kernel Release <= 3.0F

The SELECT statement has the following form:

SELECT f1 ... fn FROM tab INTO ... FOR ALL ENTRIES IN ftab WHERE ...

You must therefore use an f1 ... fn field list. The error does not occur for "SELECT *". Furthermore, the statement must be small enough so that it only results in a single SELECT database command. If the internal table, ftab, has a large number of lines, the command is implemented with several SELECT commands in the database and the error does not occur.

3. Kernel Release <= 3.0D

The SELECT statement has the following form:

SELECT * FROM tab INTO CORRESPONDING FIELDS OF ...

FOR ALL ENTRIES IN ftab WHERE ...

Even though the "SELECT *" statement is used, the INTO CORRESPONDING FIELDS or APPENDING CORRESPONDING FIELDS additions are used simultaneously. With this constellation, the same problem may occur as described in point 2 above for releases up to and including 3.0D.

4. Kernel Release <= 4.6D

In the case of a SELECT with FOR ALL ENTRIES IN itab, the same internal table "itab" is also used in the INTO clause. As a result, the contents of the "itab" table are overwritten before they are fully processed. An incorrect selection occurs as a result. Even though it has been documented that the same table cannot be used as an input and output table, a syntax error does not occur. In Release 4.6D, there is a syntax warning as of the patch with the patch text "Open SQL: allow longer LIKE pattern".

Solution

Cause 1: As a "workaround", you should delete the duplicate fields in the SELECT list.

This problem is solved with a kernel patch:

Release 6.10:

Patch text: "Open SQL: FAE and duplicate fields in select list".

Releases 4.6D, 4.5B:

Patch text: "Scope of table names in subqueries".

Releases 4.0B:

Patch text: "Scope of table names in subqueries".

The kernel patch is currently being developed.

Releases 3.1I:

Patch text: "Open SQL: FAE and duplicate fields in select list".

The kernel patch is currently being developed.

Causes 2 and 3:

Change SELECT f1 ... fn to SELECT DISTINCT f1 ... fn.

If the UP TO n ROWS addition is also used, refer to note 51572.

Problem 2 is corrected as of Release 3.1G.

Problem 3 is corrected as of Release 3.0E.

Cause 4:

Use different internal tables. This problem is solved as of Release 6.10.

Cheers

VJ

[FredericGirod]

Hi,

first, check the number of line before use the FOR ALL ENTRIES.

Second, your example is quite strange, all you do is like to make a select with the OBJEK = '000000000013222869' in the AUSP table.

Rgd

Frédéric

[Former Member]

Hi,

I guess u need to check the contents in table ausp for such condition..

The Return Code is SELECT are as follows:

SY-SUBRC = 0:

The result table contains at least one record.

SY-SUBRC = 4:

The result table is empty.

Regards,

Tanveer.

Please mark helpful answers

[Former Member]

Try:

Select cuobj "OBJECT KEY
  objek "MATERIAL NUMBER
  from inob into table lit_inob
  where <b>cuobj</b> = '000000000013222869' and klart = 'Z01'.

Rob

[Former Member]

Hi subhash,

if value is there for that entry then try like this

where objek = <b>'%000000000013222869%</b>' and klart = 'Z01'.

dunno if it ll work, but just try out as objek field is 50 characters long.

Regards,

Bikash

[Former Member]

Actually my first Query is working fine but my query on AUSP table is failing.

I dont know the reason behind it.

[FredericGirod]

Did you check if you have FIELD EXIT ?

(that means, you will show value in result of SE16 that is not in your table, SAP convert this data using a field-exit. The best example is the WBS)

Rgd

Frédéric

[Former Member]

Are you saying that this code returns sy-subrc = 0:

Select cuobj objek 
  from inob into table lit_inob
  where objek = '000000000013222869' and klart = 'Z01'.

In our system, it's cuobj that is 18 characters long and has a lot of leading zeroes, not objek.

Rob

[Former Member]

yes sy-subrc = 0 in this case

The number of character doesnt make a difference in this case Rob

[Former Member]

Hi Subhash,

In my system the code working fine. I have just passed the values which are there in my system. Check your data once again.

Thanks,

Ramu N.

[Former Member]

Hi subhash

The problem might be with the format of ATINN field. Try using..

DATA: atinn LIKE ausp-atinn.

CONVERSION_EXIT_ATTIN_INPUT

EXPORTING

input = 'C02000' "used for material category

IMPORTING

output = <b>atinn</b>.

and then..

select atwrt "Characteristic value

into table lit_ausp from ausp

for all entries in lit_inob1

where objek = lit_inob1-objek

and atinn = <b>atinn</b>

and klart = 'Z01'

and atwrt ne '00001'.