Curious case of FAE (For All Entries)

Curious case of FAE
Share on Facebook11Share on LinkedIn4Tweet about this on TwitterShare on Google+1
Please Share!!

I always explain this concept to all my junior team members. The importance of selecting all the primary keys of a table while doing a FAE, even though we might not use some of the key fields later in the logic.

If we do not select all primary keys, there would be data loss at run time and it would be difficult to figure out the root cause, if the volume of data is big.

There are numerous documents on this topic. And I do not want to flood the already over crowded topic with theory. Here, I am trying to provide the run-time screenshots and results, so that even the non technical personnel would understand the concept (specially our young ABAPers).

[adToAppearHere]

Example: Total 24 entries are present in MARC table for material 106599.

MARC

Case 1: FAE select without all primary keys

FAE Code1

Execute the above code snippet with one material:

SS1

Runtime behaviour of FAE select: Check only two records are selected from the MARC table. Where are the rest 22?DB1

SAP internally selects 24 entries with these two columns (matnr and mmsta in this case), but before it passes it to the internal table, it deletes adjacent duplicates. Since only one primary key is selected, in this process, rest 22 rows which are replica of row 1, i.e. matnr 106599 and mmsta blank, they all get removed.DB2Had the second primary key, werks, would have been selected as well, then all 24 entries would have been unique and it would not have been deleted internally and would have returned correctly.

Case 2: FAE select with all primary keys

FAE Code2

Runtime behaviour of FAE select: Check all 24 records are selected from the MARC table.

DB21

DB22

Hope you understood the importance of selecting all primary keys. So, please make a note, there is no harm in selecting all the keys. In fact, it is a good programming practice to select all primary keys whenever multiple rows of data are being fetched from any table… with FAE or normal select.

You might like to check our other post on Good Programming Practice.

Image source : via Free Stock Photos foter.com

Share on Facebook11Share on LinkedIn4Tweet about this on TwitterShare on Google+1
Please Share!!

About the Author

SAP Yard
SAP Yard
SAPYard is one stop page for all Technical Folks in SAP. You would find un-conventional explanations, tutorials, and tricks. Please like our Facebook Page and also join our LinkedIn Group.

7 Comments on "Curious case of FAE (For All Entries)"

  1. What are the value of the var SY-DBCNT in each case ?

  2. Shishira Shastri H | January 10, 2016 at 5:49 am |

    Thanks for the useful tip and visual explanation.

  3. krishnamraju | December 29, 2015 at 3:11 pm |

    very good one…and this may one of the drawback to (FAE)……
    so i have a doubt (this may another drawback to (FAE)) that whenever using for all entries the driver table should not be empty if it will empty then table2 will fetch all records frm table1 ….even with second select stm have where cond…….
    for this we r loop at low table(it_vbak).
    fill sign ,option,low…etc
    append to r_kunnr(range).
    endloop.
    delete it_kna1 where kunnr not in r_kunnr(range).
    but this is performance issue for puting loops….how can i do it… in better. way.

    • Dear Krishna – True, Before doing FAE, we should always check if the driver program is not empty. If it is empty, you should not be selecting it.

      Why would you need KNA1 (KUNNR) data if nothing is there in VBAK. I did not understand the requirement. But, I believe, we can avoid the loop and delete..

      Regards,
      Team SAPYard.

  4. shubham garg | August 16, 2015 at 5:31 am |

    Please make an example for CDHDR AND CDPOS TABLE with for all entries select statements where inputs will be tabname fname from cdpos and udate from cdhdr and output will be the list of material that got changed.
    As here in cdhdr there are 3 key fields and in cdpos there are about 7 key feilds and also want to know which of the fields will come in select clause and which of the field will cime in where clause and why ?

    • Sure Shubham.. We will create a case study with your example and update our post.. Thank you for your feedback.

      Please keep visiting.

      Regards,
      Raju.

Comments are closed.