Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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 &apos;Language Polygons&apos; 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?

Thanks for any help.

share|improve this question
1  
What is the data type of the 'ID' field? –  mwalker Oct 1 '10 at 22:32
    
Try the following, instead of the current LEFT method you're using, to see if it helps: LEFT(CAST(ID AS VARCHAR) , 3) –  Michael Todd Oct 2 '10 at 15:19
    
@mwalker: in ArcMap the data type in Text (length: 10). In SQL Server it's nvarchar(10). –  LarsH Oct 4 '10 at 14:53
    
@Michael Todd: Thanks for the suggestion. I tried that; no change in behavior. –  LarsH Oct 4 '10 at 15:00
1  
You can create a multi-versioned view if you need to access the data outside the context of ESRI products. The only problem I have with that is that it seems to add several seconds to a query that should take microseconds (so it's a bit slow, but it still works). Again, this would just be a workaround until someone can figure out why you're having this issue. –  Michael Todd Oct 6 '10 at 2:27
show 10 more comments

2 Answers

As a temporary workaround (since you're accessing the data via non-ESRI products), you can use a multi-versioned view to access the data.

A multi-versioned view will help because it takes into account the deletes and adds that represent each version; that way you don't have to worry about missing data when reading directly from the layer table via SQL.

share|improve this answer
add comment

I know in ArcGIS Desktop, ArcSDE field names must be enclosed in double quotes. Maybe this SQL reference will help.

share|improve this answer
    
Thanks for this reference. Strangely, when I click on the Help for "Select by Attributes" in ArcMap, it says that with ArcSDE dbs there should be no quotes or brackets. But the ref you gave said there should be double quotes. However, I tried the REST query with double quotes, and it still didn't work. –  LarsH Oct 4 '10 at 14:59
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.