Sorting problem while code is the same

[former_member2492]

I have written the same program in two systems,

the code is identically the same ,but when I download the file(excel) ,both data is the same but one is sorted differently from the other...IDK why!

Does SAP have a sorting parameter in the system,that differentiates from one system to the other?

Please let me know if you too have encauntered this problem before!

[FredericGirod]

The Hana database (and apparently most of the database) does not make a default SORT in the SELECT statement,

So you have to add to all your SELECT statements: ORDER BY PRIMARY KEY

you could have issu if you do not have the fields of the key specified in the selected field. In this case you have to specify the list of the key field present in the selected fields.

[former_member2492]

Dear user one system is development and the other is sandbox,is this still related?

[FredericGirod]

try after updating your code

[former_member2492]

I am in testing,once confirmed I will accept the answer.

[Sathya_Gunasekaran]

Are both or one of your systems on HANA?

[FredericGirod]

sathya.gunasekaran3 even if the both are in Hana, the result will be different, Even between Dev / Qual & PROD, if you do not manage the ORDER, you could have not reproductible error between systems

[former_member2492]

Dear users one system is development and the other is sandbox frdric.girod sathya.gunasekaran6

[Sathya_Gunasekaran]

frdric.girod , Yes I understand thats why I asked are both or one of their systems on HANA 🙂

[Sathya_Gunasekaran]

johnu951 - What is your DB?

[michael_piesche]

Any relational database management system does not guarantee you a sorted order (by for example the primary key) with a select statement, if you don’t request a sort order.

Even if it „works“ a hundred times, it is not guaranteed.

You either have to assign a sort order to the select statement or sort your internal table manually, or define your internal table with type SORTED.

[former_member2492]

I am in testing,once confirmed I will accept the answer.

[matt]

Thank Michael. I was beginning to think it was only me who understood this!

[michael_piesche]

johnu951, if you ever had classes on RDBMS and SQL, that is one of the first things you get to learn, that an order is only established by the ORDER BY clause and otherwise it is 'random'.

Developers like to be lazy, so trust me, I would love to avoid making sure of a sort order if I could rely on a database select to do that for me. But it has to do it "EVERY TIME" for me, if i have to rely on it, otherwise I have constructed coding that has a bug.

I have actually encountered it in a system where a simple select didnt return the values in a sorted order of the primary key. The reasons for this can be ABAP (e.g. a partially buffered table) or also settings in the RDBMS in general or for the specific db or because of its size. You would need to dig really deep in those cases to find out why it is not sorted by key "like it always" is.

Is all that really worth the time being spent on? But I guess everybody has to experience this for themselves in order to believe it, I did too, even though I should have known better 😉

[Sandra_Rossi]

Sathya Gunasekaran So, if you understand, why do you care about the database systems? That's not relevant for the question. It's probably just a SQL question here. (or does HANA introduces an automatic sort order, just for the pleasure to provide a non-standard SQL?)

[Sathya_Gunasekaran]

sandra.rossi : Before saying that the addition of explicit orderby to the SQL will solve the problem, I wanted to make sure the db was HANA

[matt]

Dear programmers. Please understand that there is no defined order on an referential database. Whether Oracle, ADABAS, Informix or HANA.

There never has been.

If you do not specify ORDER BY, the result set order is simply not defined. Experimentally, you may find they're in primary key order, but I've explicitly seen it on an Oracle database where this wasn't the case and the code broke.

[JackGraus]

From the problem raised it is not clear this is a SQL select order issue or internal table sorting issue.
When looping over an internal table the order of records is random. Just as is the case for a SQL select. For internal table that can be solved by using or creating a (secondary) key and using that key when looping over the table. The Excel line number should then be a part of that table key.

[michael_piesche]

johnu951, please follow up on your open question.

• comment answers or your question if there are still open issues.
• otherwise mark an answer as accepted if it helped you solve your problem
• or post an answer of yourself and accept it if you found another useful solution yourself
• or redirect your question to another question that is related and was useful to solve your problem
• in the end, close your question