topic Re: Incorrect sort sequence while fetching from Database in Application Development and Automation Discussions

Incorrect sort sequence while fetching from Database

Former Member — Fri, 24 Jun 2011 10:21:39 GMT

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

Re: Incorrect sort sequence while fetching from Database

matt — Fri, 24 Jun 2011 10:29:39 GMT

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

Re: Incorrect sort sequence while fetching from Database

Sandeep_Panghal — Fri, 24 Jun 2011 10:39:32 GMT

You can order by .

Re: Incorrect sort sequence while fetching from Database

Former Member — Fri, 24 Jun 2011 11:25:18 GMT

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

Re: Incorrect sort sequence while fetching from Database

SuhaSaha — Fri, 24 Jun 2011 11:34:55 GMT

Hello Sarves,

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

BR,

Suhas

Re: Incorrect sort sequence while fetching from Database

Former Member — Fri, 24 Jun 2011 12:18:26 GMT

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

Re: Incorrect sort sequence while fetching from Database

Former Member — Fri, 24 Jun 2011 12:24:11 GMT

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

Re: Incorrect sort sequence while fetching from Database

matt — Fri, 24 Jun 2011 13:17:55 GMT


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.