Skip to Content
author's profile photo Former Member
Former Member

Sorted in query by default

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Best Answer
    Posted on Jul 14, 2011 at 10:12 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • 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

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.