Find last matching result using "read table with key"

Question

I need to find the sy-tabix of a the last entry in an internal table that matches v_key = x. I'm trying to do it with:

read table i_tab with key v_key = x

But since there are multiple entries in the table that match v_key = x, how can I make sure I get the sy-tabix of the last matching entry? I can't search by another key unfortunately.

Rationalize

Two possibilities: Either use LOOP AT ... WHERE v_key = x, SY-TABIX inside the loop at the last iteration will give you the line number. Or calculate the next possible value of x at byte level (byte+1), sort your table (with preference to a table with a sorted key), do a READ TABLE with "X+1", which will return SY-TABIX of the next line, so just subtract 1. – Splenitis

Answer 1

READ TABLE is for reading single lines, for more lines you have to use LOOP:

LOOP AT itab
     ASSIGNING ...
     WHERE vkey EQ x.
ENDLOOP.

Right after the LOOP sy-tabix will contain the last line, where the condition is true.

As it was pointed (see discussion below), for the best performance there has to exist a NON-UNIQUE SORTED key (either primary or secondary) for this field

Ambuscade

That works, but seems to get quite slow depending on the size of the table. Isn't there a faster method? – Rationalize

@suimon, cf my comment, 2nd possibility. – Splenitis

@SandraRossi I don' even have an idea what that is or how it works :/ But thanks, I'll google! – Rationalize

@suimon cf comment converted into an answer with addendum to provide more details – Splenitis

of course, with LOOP AT, itab must be a table declared TYPE SORTED TABLE OF ... WITH NON-UNIQUE KEY v_key for an optimum performance. Moreover, if you have many lines with the same v_key value, then the performance is not optimum (cf my answer for a better approach) – Splenitis

@JozsefSzikszai maybe it's worth adding a remark about performance directly inside your answer. – Splenitis

Answer 2

Another possibility, which is useful if you have many lines with the same v_key value.

First, make sure a line exists for X. If it's not found, then no need to pursue.

Calculate the next possible value (variable x_next_value) of the searched value (variable X). Examples:

• If X is an integer, simply search X + 1. Example: for value 5, x_next_value will be 6.
• If X are characters (C or string), then get the number of the last character (cl_abap_conv_**out**_ce=>uccpi), add 1, and update the last character (cl_abap_conv_**in**_ce=>uccpi).
• Same kind of logic for other types of X.

Make sure your table is sorted (with preference to a table declared sorted table of ... with non-unique key v_key)

Then do READ TABLE itab WITH KEY v_key = x_next_value.

Important : even if no line is found, SY-TABIX will be set to the number of next line after all the lines having v_key = x (cf ABAP documentation of READ TABLE - Possible values for SY-SUBRC and SY-TABIX)

Pseudo code :

READ TABLE ... WITH KEY v_key = x_next_value.
" eventually BINARY SEARCH if itab is STANDARD instead of SORTED

CASE sy-subrc.
  WHEN 0.
    last_tabix_of_x = sy-tabix.
  WHEN 4.
    last_tabix_of_x = sy-tabix - 1.
  WHEN 8.
    last_tabix_of_x = lines( itab ).
ENDCASE.

Note : exactly two READ TABLE are needed to find the last matching result.

Splenitis

it's not work if the key is not a sequence, if the table have keys (1,3, 8, 10) simple add 1 to key will fail. – Epistyle

@Epistyle In your example, searching for "9" will make READ TABLE "fail" with SY-SUBRC = 4 but at the same time SY-TABIX is set to 4 (point at line "10"), because it's the line with a value just above "9" - cf ABAP documentation of READ TABLE - Possible values for SY-SUBRC and SY-TABIX – Splenitis

Answer 3

I think fastest way is

Sort itab by key.
read table itab with key key = v_key
                binary search.
loop at itab assign <fs> from sy-tabix.
   if <fs>-key ne v_key.
      exit.
   endif.
endloop.

Epistyle

Why not using a sorted index (primary or secondary key) so that your code has the same performance, but is smaller (same as code shown by @JozsefSzikszai) – Splenitis

Answer 4

I am writing a different solution which might be helpful to you.

add one column keyno in table i_tab.

When you are inserting records in table i_tab, and there are multiple records to append in table i_tab for same key, you can add keyno for each records where same key has multiple records.

For Example:

Insertion of records in Table i_tab

i_tab_line-key = 'X'.
i_tab_line-keyno = 1.
APPEND i_tab_line to i_tab.
i_tab_line-key = 'X'.
i_tab_line-keyno = 2.
APPEND i_tab_line to i_tab.
i_tab_line-key = 'X'.
i_tab_line-keyno = 3.
APPEND i_tab_line to i_tab.

Table i_tab Sorting by Key Keyno descending.

   SORT i_tab by key keyno Desc.

Now Read Table will find last matching entry from table i_tab for the key.

   read table i_tab with key = X

regards,

Umar Abdullah

Rockoon

And what if OP doesn't populate this table and cannot add own field? – Westnorthwest

Answer 5

sort i_tab by v_key .
read table i_tab with key v_key = x binary search.

while i_tab-key = x
  lv_tabix = sy-tabix + 1 .
  read table i_tab index = lv_tabix .
endwhile.

result = lv_tabix -1 .

Reddish

you are right, it is not slow. Add a sort i_tab, and it will even give the right result. It will work identical to @I.B.N.'s answer, just a bit more awkward. – Fresh

@Epistyle solution is O(n) for the loop part. It needs a secondary key to be O(log n ). However, this solution is O(1) by default, no need to define a secondary key. It is far better. – Reddish

READ TABLE i_tab is indeed O(1), but you call it in a WHILE, making it O(n). It is identical to the solution of @I.B.N, just not as readable. – Fresh

@András you see Index used in the "read table" lines. It is O(1). period. – Reddish

it is o(1) per executions. You do more than one, if all key fields equal x, you do n. Exactly as I.B.N... – Fresh