Sorting on Query Layer Selections - Failing

[MichaelVolz]

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.

[JoshuaBixby]

I am seeing the same thing with ArcGIS 10.6 and SQL Server 2016.  I am not sure if this is expected behavior or not.  It is probably worth a call to Esri Support.

[MichaelVolz]

Joshua:

Have you used query layers in previous versions of ArcMap?  If so, did you ever encounter this issue back then or is this the first time you are seeing this issue?

Is your org using Pro?  If so, do you know if the same issue is occurring in Pro at whatever version you might be at?

[JoshuaBixby]

Michael,

Personally, I occasionally use query layers and have since they were introduced several version ago.  Collectively, there is some but not a lot of use in my organization.

In terms of this issue, I don't recall seeing it in previous versions of ArcGIS; but honestly, I never noticed in the current version until I read your question.  I guess I don't sort on selection sets from query layers very much, or at all!

I just tested with Pro 2.1.2, and I see the same behavior as ArcMap 10.6.  I could test against Oracle 11gR2, but I suspect it will be the same.

[MichaelVolz]

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.

[JoshuaBixby]

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.

[MichaelVolz]

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.