Incorrect sort sequence while fetching from Database

[Former Member]

Hi,

When I am trying to fetch records from database table to table to internal table, the records are in jumbled sequence.

Example:

SELECT * 
  FROM vbak 
    UP TO 100 ROWS 
  INTO TABLE itab3 
 WHERE vbeln in s_vbeln 
  AND posnr in s_posnr.
IF sy-subrc EQ 0.

ENDIF. 

The sequence what i see in SE11 is differing in internal table.

Thanks

Sarves

[matt]

The order in the database is not defined. You must use ORDER BY in your SQL or sort your internal table.

[Former Member]

Matt,

This is in continuation to my above question. Now i see that even in SE11 when i give maximum number of records as 100 it fetches me different records and when i give it as 200 it fetches me different. To be more clear see the below example.

i have sales orders 1 - 65000.

when i give 100 as maximum number it fetches records 1-95 and remaining 5 records are 64,001 to 64,005.

when i give 200 as maximum number it fetches records 1-195 and remaining 5 records are 64,001 to 64,005.

The Database is DB2.

Thanks

Sarves

[SuhaSaha]

Hello Sarves,

What you see in the data browser of SE11(or SE16) is a report output & not the actual DB snapshot!

BR,

Suhas

[Former Member]

Hi Sarvesh,

This is because of the algoritham used by the database.

This search will use the DIVIDE and FETCH rule for data fetching, so some of the entries will be taken and rest will be skipped.

Thats the reason why you are getting a part in the select with 100 and another part with 200.

specifying the WHERE condition is the only possible way.

If you declare the internal table as SORTED, the data that you are fetching will be the same and only thing thing is it will be as SORTED in the internal table.

Regards

HM

[matt]

Matt,
> 
> This is in continuation to my above question. Now i see that even in SE11 when i give maximum number of records as 100 it fetches me different records and when i give it as 200 it fetches me different. To be more clear see the below example.
> 
> i have sales orders 1 - 65000.
> 
> when i give 100 as maximum number it fetches records 1-95 and remaining 5 records are 64,001 to 64,005.
> when i give 200 as maximum number it fetches records 1-195 and remaining 5 records are 64,001 to 64,005.

My earlier statement stands: The order in which records are returned from the database is undefined.

Two identical SQL calls at different times can result in different results, if you don't return the full result set. When you use the data browser, you are not guaranteed to get the same result set if you change the number of records to retrieve - or even with two calls for the same number of records.

[Sandeep_Panghal]

You can order by .

[Former Member]

order by is expensive. SAP recommends a simple sort by statement after db retrieval.