I'm having a problem using the SQL RRN() scalar function in the Order By clause of a SQL Server Select statement.
The SQL Server puts a request "Select * from Logicalfile where .... Order By RRN(Logicalfile)" to the IBM i.
But the record headers that are in RRN order always before the record detail, sometimes get picked up after the record detail is (contrary to the Order By RRN() displaying on the IBM i ).
This happens usually once a night where DB monitor shows a ID code=3002 of deciding it's own access path.
The logicalfile is not needed as the SQL statement handles the full selection in the Where clause. In Interactive SQL, using Order By RRN(logicalfile) or RRN(physicalfile) always returns RRN() sort order. I don't know if going an extra step of adding a pseudo-column of RRN() to the SQL statement and then Order By on that pseudo-column? The Order By RRN() theoretically should work 100% of the time.
My IBM i is at v7r1.