Update: This only occurs when the layer being queried has a join. It doesn't matter if it has a join to a different database or not. And the query that fails is not trying to access columns in the joined table.
In order to more accurately express the problem, I've used fully-qualified (4-part) column names below where required. See the updated WHERE clause below.
Original question:
We are using ArcGIS Server 9.3.1, Java, and ArcSDE with SQL Server 2005 sp2.
It would be very useful for us to be able to monitor our data periodically for defects, with a query such as
select * from sdeDLM.SDE.Lang_Polygons
where ISO_LANGUAGE_CODE_ID <> LEFT(ID , 3)
In ArcMap, I can do that query using Select by Attributes. I enter the above where clause and click 'Verify', and the expression is verified successfully.
But in order to monitor the data we need to use the REST API. We can successfully do REST queries with a where clause such as
WHERE sdeDLM.SDE.Lang_Polygons.ISO_LANGUAGE_CODE_ID IS NULL
or just
WHERE ISO_LANGUAGE_CODE_ID IS NULL
Those work just fine. FWIW, the URL for that REST query is:
http://ourServer:8399/arcgis/rest/services/DLM21_query/MapServer/2/query?text=&geometry=&geometryType=esriGeometryEnvelope&inSR=&spatialRel=esriSpatialRelIntersects&where=ISO_LANGUAGE_CODE_ID+is+NULL&returnGeometry=false&outSR=&outFields=&f=html
But when I try a REST query with a where clause like
WHERE sdeDLM.SDE.Lang_Polygons.ISO_LANGUAGE_CODE_ID <> LEFT(sdeDLM.SDE.Lang_Polygons.ID , 3)
the REST API returns the message:
Unable to perform query. Please check your parameters
As best I can tell, it's the LEFT() function that makes the difference. I have been able to successfully use where clauses with everything else in them, including '<>'.
The REST API docs for querying a layer say that "Any legal SQL where clause operating on the fields in the layer is allowed."
The URL for the query that gives an error is:
http://ourServer:8399/arcgis/rest/services/DLM21_query/MapServer/2/query?text=&geometry=&geometryType=esriGeometryEnvelope&inSR=&spatialRel=esriSpatialRelIntersects&where=sdeDLM.sde.Lang_Polygons.ISO_Language_Code_ID+%3C%3E+Left%28sdeDLM.sde.Lang_Polygons.ID%2C+3%29&returnGeometry=true&outSR=&outFields=&f=html
Is there a way to get the REST API to accept a where clause using Left()? Or a way to find out why it's not working?
I tried browsing the latest logs in C:\Program Files\ArcGIS\server\user\log. The file Server-20100929-143218.dat shows the following output from the time when the query failed:
String request received. Request size is 2629 characters.
QueryFeatureData2 has started.
Preparation for method: QueryFeatureData2 has started.
Preparation for method: QueryFeatureData2 has completed.
Query Operation for method: QueryFeatureData2 on layer 'Language Polygons' has started.
**GeoDatabase Error :An invalid SQL statement was used.**
QueryFeatureData2 has completed.
Method failed.HRESULT = 0x80040207 : This is a FACILITY_ITF error that is specific to the interface that returned this error. See the documentation of the interface that returned this error for information about this HRESULT.
ERROR INFO = An invalid SQL statement was used.
But why is the SQL statement invalid? Clearly the WHERE clause has been tested as valid elsewhere. And why would some of my WHERE clauses be accepted as valid, but not the one with LEFT() in it?
