Database table Select

[Former Member]

Dear Gurus,

  I am facing a strange issue where I have a select from a custom table

    SELECT *
  FROM ZXXX

  INTO TABLE t_ZXXX.

IF sy-subrc = 0.

ENDIF.

If I put a breakpoint at IF sy-subrc = 0 in Development environment and Quality Environment and debug, I can see that the entries are in different sequence in the two environment (I thought if there is no sorting on the internal table, default the entries will be sorted based on the primary keys of the database table). However, there is no sorting on the internal table. Is there any specific reason for this or some BASIS settings. Thanks for the help in advance.

[former_member188827]

The order in which SELECT statement fetches records from database can vary if you dont use "ORDER BY" addition.In order to sort data fetched using SELECT statement by primary key give addition:ORDER BY PRIMARY KEY.

SELECT *
  FROM ZXXX

  INTO TABLE t_ZXXX

ORDER BY PRIMARY KEY.

The "ORDER BY" addition can impact query performance, so consider sorting internal table data after data is read from database.

Regards

[Former Member]

Hi Subhajit,

The underlying database is generally relational one and the sequence of records fetched via SELECT is random and does not follow any pattern.

If you want it to obey any patter use Clauses like ORDER BY <any field>.

BR.

[ThomasZloch]

I believe that the sequence is not random, but according to an internal ID specific to the database (for Oracle: ROWID), so essentially the entries would appear e.g. in the order as they were added to the database, which can be different in each system. So for a reliable program logic they will need to be sorted explicitely as described.

Thomas

[Saha_Arpan]

Hi,

Whenever SAP selects data from any database table, it selects data after matching the where conditions with the index: primary and secondary. If it finds one which matches the criteria, it automatically selects data based on that, and sorts the data as well.

Regarding your case, can you see if you have any key maintained for your table????

Arpan

[Former Member]

Hi,

Yes you can use the "ORDER BY" clause. But my advice is to add a SORT statement after the fetch to reduce the overhead on the database.

[Former Member]

Thanks for all your response. Actually it is an old program and used to work fine earlier. That is strange. I think I need to sort it by the primary key after the select is done and then check. Please confirm.

[RaymondGiuseppi]

There are many reasons that can change the order data are read from database, so better use ORDER BY or SORT the data.

I remember an old note following a system update (was on iSeries, Note 743113 - iSeries: Known Issues with V5R3M0) there are many similar notes on the same problem with almost every database.

5. Correct the report(s) by adding the 'ORDER BY' clause to the
   SELECTs. If the clause is not specified then the records may be
   returned in a random sequence. Even if the records are usually
   returned in primary key sequence, you should not rely on this
   happening.

Regards,

Raymond