Sorted in query by default

[Former Member]

Hello experts,

When we make a select in ours "Z" programs, the result of the query is sorted by primary key of table or by the index that DB2 use for execute the query.

Now we are changing our database system to Oracle (and SAP version) and the result of the query isn´t sorted like DB2, this configuration is the database or SAP? Anyone knows any parameter to configurate this behavior?

Thanks in advance,

Hans

[markus_doehr2 · Accepted solution]

Sort results in ABAP are defined by the codepage on the system. Was just the database or also the operating system changed? Did a Unicode migration take place?

Markus

[Former Member · Accepted solution]

Hi Marcus,

Yes, these month we changed all of our systems and applied a unicode migration.

The configuration are:

- In the past: SAP 4.7 + DB2 version 8 + Z/OS

- Now: SAP ECC 6.0 + Oracle 11.2 + Linux

The programmers assumed this sorting by default. Now, it has disappeared thus some client programs do not work.

Is posible change the configuration of the system without change all the programs?

Thanks again,

Hans

[volker_borowski2 · Accepted solution]

The programmers assumed this sorting by default. Now, it has disappeared thus some client programs do not work.
Is posible change the configuration of the system without change all the programs?

Hi,

now this is really a bad and WRONG assumption.

Any SQL statement on any database will NEVER have a sorted resultset FOR SURE, but just accidently,

unless an ORDER BY is specified. This is simply, because the DB checks the statement and then

evaluates the cheapest way of getting the result.

If no ORDER BY is given, why should the DB execute an expensive SORT operation.

I had the very same thing whithin a switch from Oracle 8.1.7 to 9.2, when the optimizer changed from RULE to COST_BASED.

Quite a couple of plans changed and quite a couple of result sets changed their sortorder, much to the

confusion of a developer assuming the same thing.

And then two years ago, the very same thing within a PATCH from 10.2.0.2 to 10.2.0.4.

The very same developer and a group by statement.

"... If i use GROUP BY, the result is automaticly sorted ..."

WRONG

It was automaticly sorted before 10.2.0.4, because 95% of the plans where using a SORT-GROUP-BY approach.

With 10.2.0.4, oracle introduced the HASH-GROUP-BY approach, and if you like your grouped resultset sorted,

you need to specify GROUP BY and ORDER BY.

Summary: If you want a sorted result in ANY statement, you MUST specify ORDER BY.

Volker