topic FOR ALL ENTRIES sorted. in Application Development and Automation Discussions

FOR ALL ENTRIES sorted.

Former Member — Tue, 03 Apr 2007 22:12:08 GMT

Hi all,

The resulting list is not necessarily sorted even if the FOR ALL ENTRIES itab is sorted. How can make sure the optimizer will process the FOR ALL ENTRIES in the itab's sorted order?

For example, I would like to get the 10 most recently created sales orders' PO #:

sort itab by vbeln descending.  " has 100 entries sorted to the most recent
select bstnk up to 10 rows
  from vbak
  into table itab_bstnk
   for all entries in itab
  where vbeln = itab-vbeln.
*-- the resulting list in itab_bstnk is not the same order as their corresponding vbelns

I cannot use ORDER BY clause because of FOR ALL ENTRIES. Would declaring the itab as a sorted internal table help?

Thanks a lot,

John

Re: FOR ALL ENTRIES sorted.

Former Member — Tue, 03 Apr 2007 22:29:28 GMT

Unfortunately without an 'ORDER BY', SAP will have the internal table just as they are ordered in the database table that you are accessing from. So in your case, it will be in the order of VBELN ascending as that is the key and it is sorted ascending in the VBAK table.

Instead of sorting your itab, add VBELN to your select and extend your internal table itab_bstnk to include VBELN and then sort itab_bstnk by VBELN descending.

SELECT VBELN BSTNK UP TO 10 ROWS FROM vbak

INTO TABLE itab_bstnk FOR ALL ENTRIES IN itab

WHERE vbeln = itab-vbeln.

SORT itab_bstnk BY vbeln DESCENDING.

Re: FOR ALL ENTRIES sorted.

Former Member — Tue, 03 Apr 2007 22:44:24 GMT

My problem is that since the UP TO 10 ROWS will pick only 10 out of 100, the SELECT doesn't necessarily picks the 1st 10 entries of itab_vbeln, therefore the resulting list is not the most recent sales orders.

SAP will not even pick up the records in ascending order or the database order in VBAK. Thus this question.

Re: FOR ALL ENTRIES sorted.

Former Member — Tue, 03 Apr 2007 22:50:40 GMT

So is your ultimate requirement is to get the most recent 10 sales orders from out of the 100 sales orders in that itab? In that case, you have to get all the 100 sales orders, sort them and then delete the rest 90. I am not sure if you will be able achieve all this with just the SELECT statement alone.