topic Database table Select in Application Development and Automation Discussions

Database table Select

Former Member — Fri, 12 Jul 2013 14:03:46 GMT

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.

Re: Database table Select

former_member188827 — Fri, 12 Jul 2013 15:17:35 GMT

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

Re: Database table Select

Former Member — Fri, 12 Jul 2013 19:07:02 GMT

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.

Re: Database table Select

ThomasZloch — Fri, 12 Jul 2013 20:10:00 GMT

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

Re: Database table Select

Saha_Arpan — Sun, 14 Jul 2013 18:36:25 GMT

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

Re: Database table Select

Former Member — Mon, 15 Jul 2013 04:16:25 GMT

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.

Re: Database table Select

Former Member — Mon, 15 Jul 2013 08:01:43 GMT

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.

Re: Database table Select

RaymondGiuseppi — Mon, 15 Jul 2013 09:09:32 GMT

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