topic Re: Sorting problem while code is the same in Application Development and Automation Discussions

Sorting problem while code is the same

former_member2492 — Fri, 28 Feb 2020 09:04:39 GMT

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!

Re: Sorting problem while code is the same

FredericGirod — Fri, 28 Feb 2020 09:18:10 GMT

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.

Re: Sorting problem while code is the same

Sathya_Gunasekaran — Fri, 28 Feb 2020 09:25:05 GMT

Are both or one of your systems on HANA?

Re: Sorting problem while code is the same

FredericGirod — Fri, 28 Feb 2020 09:33:24 GMT

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

Re: Sorting problem while code is the same

former_member2492 — Fri, 28 Feb 2020 09:35:12 GMT

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

Re: Sorting problem while code is the same

former_member2492 — Fri, 28 Feb 2020 09:35:42 GMT

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

Re: Sorting problem while code is the same

FredericGirod — Fri, 28 Feb 2020 09:37:23 GMT

try after updating your code

Re: Sorting problem while code is the same

Sathya_Gunasekaran — Fri, 28 Feb 2020 09:38:06 GMT

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

Re: Sorting problem while code is the same

Sathya_Gunasekaran — Fri, 28 Feb 2020 09:39:23 GMT

johnu951 - What is your DB?

Re: Sorting problem while code is the same

michael_piesche — Fri, 28 Feb 2020 12:20:49 GMT

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.

Re: Sorting problem while code is the same

Sandra_Rossi — Fri, 28 Feb 2020 13:05:42 GMT

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?)

Re: Sorting problem while code is the same

Sathya_Gunasekaran — Fri, 28 Feb 2020 13:51:40 GMT

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

Re: Sorting problem while code is the same

former_member2492 — Tue, 03 Mar 2020 08:35:50 GMT

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

Re: Sorting problem while code is the same

former_member2492 — Tue, 03 Mar 2020 08:35:58 GMT

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

Re: Sorting problem while code is the same

matt — Tue, 03 Mar 2020 10:31:15 GMT

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.

Re: Sorting problem while code is the same

matt — Tue, 03 Mar 2020 10:32:03 GMT

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

Re: Sorting problem while code is the same

michael_piesche — Tue, 03 Mar 2020 12:50:42 GMT

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 😉

Re: Sorting problem while code is the same

JackGraus — Wed, 04 Mar 2020 04:32:07 GMT

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.

Re: Sorting problem while code is the same

michael_piesche — Wed, 13 May 2020 13:35:41 GMT

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