Arcgis server is truncating the where clause when sending query to SQL server.

Question

When i make a query request to a map layer with some where clause to filter the response, the arcgis server truncates the where condition while executing the query on SQL server.

I am dealing with very large data set of approx 600 millions. The arcgis version is 10.4.1

The map layer is binded with a SQL view.

D1C7F7CF.PNG

Inked871B0F84_LI.jpg

287261B5.PNG

JoshuaBixby · Answer

This is one you will likely have to work through with Esri Support.  Looking at your screenshot of the REST API call, everything seems fine in terms of how you have structured the call.  If the REST call is valid,  the next question is how exactly the Electric Service Point layer is created.  It seems like it is a view filtering a larger data set, so then what tables are involved and what is the view's SQL?

MikeVolz · Answer

Is the GIS layer presented as a query layer using the view?  This behavior occurs with my data that is a view from a non-SDE spatially enabled Oracle database where the where clause gets dropped when sorting subqueries.  I believe in my case it has something to do with the uniqueID field that is used as the identifier when creating the query layer is not set up with the correct format.  It has been difficult to pin down and has only allowed this data to be used in limited operations instead of across the enterprise.

Did you determine the where clause was being dropped with software such as WireShark?

vikashkumar1 · Answer

I am querying map layer with following condition: where: ReadRate30Days <= 100 AND ReadRate30Days >= 0 orderByFields: ReadRate30Days DESC Ideally in SQL Sever the query should be genarted like below by arcgis server select * from OAWarehouse.Map_PM_Electric_Service_Point_View) a where ( ( ( ReadRate3Days <= 100 AND ReadRate3Days >= 0 ) ) AND ( ReportingDate >= '2019-10-09 00:00:00' and ReportingDate <= '2019-10-09 00:00:00' )) AND (Shape.STIntersects(geography::STGeomFromWKB(@P1,@P2)) = 1) ORDER BY ReadRate3Days DESC but it is generated like this in sql server select * from (select * from OAWarehouse.Map_PM_Electric_Service_Point_View) a I am not able to understand why this behaviour ?