headershadow

2 Tips to avoid most common bugs related to "FOR ALL ENTRIES"

Print Friendly

This blog is mainly intended for junior developers and consultants, who would like to discover the use of statement "For all entries" in addition to the "SELECT" command.

This statement is often used since it is a simple and practical way to fetch table records based on keys of an internal table retrieved previously.
However, you should be aware of its drawbacks to avoid 2 common bugs related to it.

Suppose you need to retrieve total values from table COSP based on a list of object numbers LT_OBJNR that has been previously determined in your program.

You will likely write you selection as follows:
SELECT OBJNR
WKG001
WKG002
WKG003
WKG004
WKG005
WKG006
WKG007
WKG008
WKG009
WKG010
WKG011
WKG012
WKG013
WKG014
WKG015
WKG016
INTO TABLE LT_WKG
FROM COSP
FOR ALL ENTRIES IN LT_OBJNR
WHERE OBJNR = LT_OBJNR-OBJNR.

It seems to be a correct SELECT statement, but unfortunately it is not the case!

This selection contains 2 drawbacks:
1. As mentioned in SAP HELP documentation, "FOR ALL ENTRIES" behaves like a "SELECT DISTINCT" statement. That means all duplicates are removed. As a consequence, some relevant entries will not appear in the selection hit list!

2. If the table used in the WHERE clause is empty (this is the table LT_OBJNR in the statement above), all rows of the source table will be selected. You guess that it causes important and unnecessary performance degradation, especially for huge tables like COSP, although no result should be returned for this statement!!!

Next are 2 simple tips to avoid these problems:

1. Declare all primary keys of your database table in your select statement. It ensures that you have no duplicates in you hit list

2. Check that the table used in the "For all entries" statement is not empty. This avoids executing the select statement if no result is expected.

After applying these tips in our example, the correct selection looks as follows:

IF LT_OBJNR[] IS NOT INITIAL.
SELECT LEDNR
OBJNR
GJAHR
WRTTP
VERSN
KSTAR
HRKFT
VRGNG
VBUND
PARGB
BEKNZ
TWAER
PERBL
WKG001
WKG002
WKG003
WKG004
WKG005
WKG006
WKG007
WKG008
WKG009
WKG010
WKG011
WKG012
WKG013
WKG014
WKG015
WKG016
INTO TABLE LT_WKG
FROM COSP
FOR ALL ENTRIES IN LT_OBJNR
WHERE OBJNR = LT_OBJNR-OBJNR.
ENDIF.


More about

2 thoughts on “2 Tips to avoid most common bugs related to "FOR ALL ENTRIES"


Comment author said

By girish on 10 October 2013 at 13:19

what is this stament

 

Comment author said

By Zhi Ning Liu on 16 September 2014 at 22:53

Hi, I am experiencing a problem with FOR ALL ENTRIES.
My select statement is:

SELECT a~appl_group a~mapping a~element_id b~element_id
INTO (ls_data_flows-appl_group,ls_data_flows-mapping,lv_source_id,lv_dest_id)
FROM axt_reg_grp_mp_p AS a
JOIN axt_reg_grp_mp_p AS b
ON a~appl_group = b~appl_group
AND a~mapping = b~mapping
FOR ALL ENTRIES IN lt_source_elements
WHERE a~source_target = 'SRC'
AND b~source_target = 'TAR'
AND a~appl_group = lt_source_elements-appl_group
AND a~element_id = lt_source_elements-element_id.

the FOR ALL ENTRIES table " lt_source_elements" is populated like this,

SELECT * FROM axt_reg_grp_elem
INTO TABLE lt_source_elements. "#EC CI_NOWHERE

the three columns "a~appl_group a~mapping a~element_id" are primary keys of table "axt_reg_grp_mp_p".

lt_source_elements table contains duplicate rows like below:

a~appl_group a~mapping a~element_id b~element_id
CRMB CRMB_INTERNAL_MOVE 000010 000020
CRMB CRMB_INTERNAL_MOVE 000010 000030

my problem is that when I run this query in one place at runtime, it drops the second row; but if I run the same query in a local report on the same system, the result set contains both rows.

What could be causing this difference ?

Thanks,
Zhi Ning

 

Leave a Reply


*