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

[vikashkumar1]

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.

[JoshuaBixby]

Is the truncation consistent in terms of length?  if so, how many characters are we talking about and what type of structure for the WHERE clause.

[Lavanyaalla]

The truncation is not in terms of length.Where clause length is less than 50 characters.

Example: ReadRate30Days <= 100 AND ReadRate30Days >= 0

[vikashkumar1]

The truncation is not in terms of length. WHERE clause length is less than 50 characters.

 Example: ReadRate30Days <= 100 AND ReadRate30Days >= 0

[vikashkumar1]

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 ?

[JoshuaBixby]

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?

[MichaelVolz]

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?