ABAP FOR ALL ENTRIES is handy, but would create lot of data inconsistencies if you don’t use it properly.
For simplest use of FOR ALL ENTRIES, you would write your SELECT query using the FOR ALL ENTIRES and use one or more fields from the table into WHERE condition.
IF t_ids IS NOT INITIAL. SELECT * INTO TABLE t_t100_all FROM t100 FOR ALL ENTRIES IN t_ids WHERE arbgb LIKE '0%' AND msgnr = t_ids-table_line. ENDIF.
When your table has few key fields, you generally tend to select them, even though you don’t need them. But when there are many Fields you tend to not include them in your table and subsequently in your SELECT query.
For example, lets see this query:
Incorrect FOR ALL ENTRIES
SELECT ryear drcrk rpmax rtcur racct rbukrs rcntr kokrs hsl01 hsl02 hsl03 hsl04 hsl05 hsl06 hsl07 hsl08 hsl09 hsl10 hsl11 hsl12 FROM faglflext INTO TABLE lt_fagl FOR ALL ENTRIES IN lt_skb1 WHERE ryear IN lr_gjahr AND racct = lt_skb1-saknr AND rbukrs = lt_skb1-bukrs.
Since this query doesn’t have all the key fields, you would run into the issues when you will try to reconcile it with some of the standard transaction FAGLB03. The issue will happen when you have same posting for same GL using same CC in different periods. In that case, system would only bring one entry instead of the multiple. On the other hand the standard transaction would get you all the entries would a different total.
When you debug the std transaction and reach to SELECT, you would think, this is the same query I have. Than why my query wont work. So you download the data from both of the ITAB – Your program and Std transaction. You compare them and you would notice few entries are definitely missing. After few rounds of VLOOKUP and Compare in excel, you suddenly realize that FOR ALL ENTRIES is dropping few entries. By the time, you are already expert in Excel.
To fix this you would than realize that you need to include more key fields into the SELECT. Next hurdle would be to explain to your Functional counterpart and your users why it was not working.
If this program was not developed by you, I can imagine how much you hate that person when you realized the root cause. A silly mistake! On bright side, you are now Excel expert
Why you need Key fields
To improve the performance, you would definitely want to have unique values in the ITAB which you are using as FOR ALL ENTIRES. If you don’t pass unique values, DB would reselect the records for each duplicate values. Finally, DB would remove the duplicates and give you the result set.
This removing of duplicate would create data inconsistencies, if you don’t have key fields in your selection fields part of your SELECT query. If you assume that all your fields would make up the unique value without including the Key fields, you are inviting trouble in the future.
Adding the Key fields of the table would ensure that all the selected records are unique. If the SELECT is a join, make sure you include all the key fields from all the tables included in the join.
Let me show you this simple example:
T100 SELECT without SPRAS
TYPES: BEGIN OF ty_t100, arbgb TYPE t100-arbgb, msgnr TYPE t100-msgnr, text TYPE t100-text, END OF ty_t100. DATA: t_ids TYPE STANDARD TABLE OF t100-msgnr. DATA: t_t100_all TYPE STANDARD TABLE OF t100. DATA: t_t100 TYPE STANDARD TABLE OF ty_t100. APPEND '001' TO t_ids. APPEND '002' TO t_ids. IF t_ids IS NOT INITIAL. SELECT arbgb msgnr text "comment to see more records are dropping INTO TABLE t_t100 FROM t100 FOR ALL ENTRIES IN t_ids WHERE arbgb LIKE '0%' AND msgnr = t_ids-table_line. WRITE: / 'Without All Key Fields', sy-dbcnt. ENDIF.
Now, this code with all the key fields
T100 with SPRAS (with all key fields)
IF t_ids IS NOT INITIAL. SELECT * INTO TABLE t_t100_all FROM t100 FOR ALL ENTRIES IN t_ids WHERE arbgb LIKE '0%' AND msgnr = t_ids-table_line. WRITE: / 'With ALL Key Fields', sy-dbcnt. ENDIF.
If you comment out the TEXT field from the 1st query, you would see more records are being dropped. This is due to the fact that, DB would only retain the unique records. DB would compare the entire record with all records and would drop the rows. Just like DELETE ADJACENT DUPLICATES FROM itab COMPARING ALL FIELDS.
Based on entries in system where I executed this:
When you use FOR ALL ENTRIES, along with other things, you would definitely want to make sure you have all the key fields in the internal table and you are selecting them as well to avoid the data inconsistencies.