SELECT UP TO 1 ROWS.. ORDER BY PRIMARY KEY OR ORDER BY FIELDS

[former_member191728]

Hi Experts,

we are doing a ECC migration to HANA DB.

As part of custom code remediation process we used FUNCTIONAL_DB variant in ATC.

In few programs the existing code was written using SELECT SINGLE without passing full primary key.

We are replacing the select single statements with SELECT up to 1 rows but when I used select up to 1 rows, the results are not matching with the old system as we know the data in tables will be in sorted order in non-HANA system but in HANA because of column architecture the sort order will be different.

I want to know whether I have to use ORDER BY PRIMARY KEY or ORDER BY <FIELDS in SELECT List>

Thanks

Pawan Akella

[matt]

1. In all except very old systems, SELECT UP TO 1 ROWS )without ORDER BY) will behave exactly the same as SELECT SINGLE. There is no point whatsoever in changing your programs. The SQL optimiser causes exactly the same SQL to be executed on the database. See here: https://wiki.scn.sap.com/wiki/display/HOME/Difference+between+select+single+and+select+up+to+1+rows which says

It used to be the case that SELECT SINGLE, and SELECT UP TO 1 ROWS had performance considerations. In most  modern database systems (e.g. Oracle) this is no longer the case - the two work identically. SELECT SINGLE is to be preferred as it is simpler coding.

2. In a relational database there is no concept of record order. That means if there are number of different records that match the WHERE clause, you might get any of them. If your programs were depending on the record order when you used SELECT SINGLE, then your programs were incorrectly written, and the fact that they worked is pure luck - it was a disaster waiting to happen.

3. There is no generic answer to your question "I want to know whether I have to use ORDER BY PRIMARY KEY or ORDER BY <FIELDS in SELECT List>" - what you do depends on the required logic.

[Jelena]

+1. I honestly see no point in such replacement all over the board. When I use partial key and don't specify anything else it means I don't care and any record will do. If it's not the case then the code was already incorrect. And even broken clock is right 2 times a day.

@Pawan - I believe you need to read ABAP Help for your release. It should be explained very clearly there what commands do what. From there you should be able to figure out what is applicable to your case.

[horst_keller]

https://blogs.sap.com/2016/06/11/select-single-vs-select-up-to-1-rows/