Searching a sorted table with nonunique key with both key fields and nonkey

once more, the last 2 comments are wrong:

> The LOOP WHERE on sorted table can only automatically use the table key, if the WHERE clause consists ONLY of conditions

> with EQUAL ( = ) which are connected by AND.

This is correct and in sync with the head of the internal table development!

Any additional needs extra handling inside the LOOP. Any IN-condition is not supported. This numerous exceptions are simply too rare and too diverse, therefore they are not supported.

And be aware, that even the support of the LOOP WHERE on sorted tables does not work in all old releases, only with 6.40 it should definitely work. Before not all operations use the keys automatically.

Siegfried

For your uderstaiding, While selecting data from a db table without specifying the primary of secondary keys , will it fetch based on the key fields ?

The answer is same for sorted internal tables too 😊

sorry, I made a mistake in the opening post

the loop statement I am planning to use is

loop at lt_vzzbepp into ls_vzzbepp
        where bukrs in s_bukrs
        and ranl in s_Ranl
        and sbewart in lt_sbewart
        and dvorgang in it_val_date
        and dbudat in it_pos_date
        and splanist eq 'I'
        and sstorno eq ''.

Hi,

thanks your Thomas for recommending my blog, this can help a little for your question, but the solution is not in the blog.

The LOOP WHERE on sorted table can only automatically use the table key, if the WHERE clause consists ONLY of conditions with EQUAl ( = ) which are connected by AND. Any other WHERE-clause which is more general (as your case) is not supported by the kernel.

You must do the optimization by yourself, there the blog can help.

Hi,

Your sorted table has non unique key BUKRS RANL.

If you put where condition on both the fields (ie Bukrs and RANL) or BUKRS it will used the binary search to get first record and upto mathing record by sequencial search.

But In case of where condition other than the which is not match left most part ot the key or non-unique key fields consider the search as linear search.

 loop at lt_vzzbepp into ls_vzzbepp
        where sbewart in lt_sbewart
        and dvorgang in it_val_date
        and dbudat in it_pos_date
        and splanist eq 'I'
        and sstorno eq ''.

In your case it will consider as linear search (ie read all the table). It will not optimize to read record.

Hi Bijo,

No. It will not use the index. It will do a linear search.

The following definition would be more productive.

DATA: lt_vzzbepp TYPE SORTED TABLE OF vzzbepp
        WITH NON-UNIQUE KEY sbewart
                            dvorgang
                            dbudat
                            splanist
                            sstorno.

Regards,

Mark

the second one will run faster.

Test it will with 100 lines in the outer table and 100 * 300 in the inner. There will be a huge difference. And in full load the first version will never come back.

But there is another issue, you internal table are too large. Sooner or later you will get a dump because of memory problems.

What is actually done with the data? Stored again in DB? Then you should split your processing into blocks, such that the table stay

smaller than 100.000 records. Process one block after the other.

>Parallel cursor, just forget it!

The blog is not the solution to your problem, it leaves the loop when the 2 internal tables are not in sync. However, you must process both tables and you must handle additional entries in both tables which are not in the other. That is hard task with high risk of bugs, even when the inner operation is a READ with LOOPs even more.

From performance perspective it is not necessary, do not forget, that parallel cursor requires identcially sorted tables. That is not necessary in the normal solution, so use sorted table and you are fine! Keep you tables as small as possible right from the beginning, sorry I thought that this is obvious!

Siegfried