can't use Left() function in query?

LarsHuttar — Fri, 10 Dec 2021 23:26:55 GMT

Hi all,

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

ISO_LANGUAGE_CODE_ID IS NULL

That works just fine.
The URL 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

ISO_LANGUAGE_CODE_ID <> LEFT(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 (http://resources.esri.com/help/9.3/arcgisserver/apis/rest/query.html) say that "Any legal SQL where clause operating on the fields in the layer is allowed."

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 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?

Thanks for any help,
Lars

topic can't use Left() function in query? in ArcGIS REST APIs and Services Questions

can't use Left() function in query?