SQL query 10.9 not working

309
3
Jump to solution
11-19-2021 09:12 AM
MatthewDriscoll
MVP Regular Contributor

According to the documentation as of 10.9, hosted feature services support SQL statements.  

https://developers.arcgis.com/rest/services-reference/enterprise/query-feature-service-layer-.html

  • Hosted feature services on a relational data store support SQL expression for the outFields parameter when supportsOutFieldSqlExpression, under advancedQueryCapabilities, is true. Hosted feature services in ArcGIS Online already support this functionality.

https://developers.arcgis.com/javascript/latest/api-reference/esri-rest-support-Query.html

  • If specifying outFields as expressions on a feature service-based FeatureLayer, the service capabilities advancedQueryCapabilities.supportsOutFieldSQLExpression and useStandardizedQueries must both be true.

But a very simply expression does not work.  PartyName LIKE '%driscoll%',

on this layer: https://www.jfksgis.us/server/rest/services/Hosted/Parcel_hosted/FeatureServer/0/

The SQL query works fine in Pro and for Online services: https://services1.arcgis.com/tpr1e2h7Rmn9C5oc/ArcGIS/rest/services/JFKS_GIS_ParcelOrion_Public_V4/Fe...

So is this not actually supported in server 10.9?

 

 

Tags (4)
0 Kudos
1 Solution

Accepted Solutions
jcarlson
MVP Frequent Contributor

Seems to work just fine, but keep in mind that these queries are two different things:

PartyName LIKE '%driscoll%'
PartyName LIKE '%Driscoll%'

Unfortunately, we can't use ILIKE to ignore the case of the input string. Using a bracketed set of characters, like "[Dd]riscoll" doesn't appear to work here either. I guess you'd have to combine two queries and put an OR between them.

jcarlson_0-1637349734741.png

 

- Josh Carlson
Kendall County GIS

View solution in original post

3 Replies
jcarlson
MVP Frequent Contributor

Seems to work just fine, but keep in mind that these queries are two different things:

PartyName LIKE '%driscoll%'
PartyName LIKE '%Driscoll%'

Unfortunately, we can't use ILIKE to ignore the case of the input string. Using a bracketed set of characters, like "[Dd]riscoll" doesn't appear to work here either. I guess you'd have to combine two queries and put an OR between them.

jcarlson_0-1637349734741.png

 

- Josh Carlson
Kendall County GIS
MatthewDriscoll
MVP Regular Contributor

Thanks.  This is a simplified problem, I have many attributes I need to query.  ESRI is logging it as an enhancement instead of a bug, even though it is a basic very simple sql statement, so it might be a long time until it gets fixed.  They need to update their documentation that  supportsOutFieldSQLExpression is not supported currently with rest endpoints.  

0 Kudos
jcarlson
MVP Frequent Contributor

Personally, if I've got a lot of attributes to sift through, I just bring the entire field into a pandas dataframe and use regular expressions. It's much easier to define a really precise, adaptable regex expression than wrangle the SQL into submission.

- Josh Carlson
Kendall County GIS