Sorting on Query Layer Selections - Failing

Question

My org is working on using query layers in ArcMap 10.5.1 for our view-only GIS data where the data originates from an Oracle 12c database that is spatially enabled but not an SDE database.  A problem I am encountering with all query layers I have tested, from trivial to more complex, is that sorting is not recognized when you are just viewing the selected records of a query in the attribute table view.  Has anyone encountered this type of issue when using query layers whether sourced from Oracle or another type of database (e.g SQL Server, Postgres, etc.)?

Any feedback on this issue is greatly appreciated.

MikeVolz · Answer

Joshua:

Thanks for that last piece of information from your SQL Server Profiler analysis.  I started a call with tech support and I will pass this information along to them about this issue.

JoshuaBixby · Answer

Michael, I took a look with SQL Server Profiler, and I see what you mean.  I don't see the query failing, though, it is simply passing a valid but incomplete SQL statement.  For whatever reason, when the software is building the selection set using IN, it isn't appending the ORDER BY clause at the end, which does happen when no selection is made.

MikeVolz · Answer

My org is seeing this behavior in Pro 2.1.2 as well.

Using WireShark we analyzed the SQL select statement that is generated when you are trying to select a subset of data and the ID of each feature is being added to the IN part of the statement which seems to be causing the query to fail every time whether in ArcMap 10.5.1 or Pro 2.1.2.