FGDB Select By Attributes — Expressions in subquery SELECT clause
ArcGIS Pro 3.2.1; file geodatabase
In Select by Attributes, this works:
objectid in (
select
objectid
from
species_records)
But this doesn't work:
objectid in (
select
objectid+0
from
species_records)
ERROR 000358: Invalid expression
Likewise, any other SQL expression throws the same error, including expressions on text or date fields: casting a date to text, string concatenation, or changing text case using the upper() or lower() functions. Even selecting a constant/literal like 1, ‘a’, or null throws an error.
In other words, simply selecting a column works, but anything other than that throws an error. I don't have that problem with mobile or enterprise geodatabases, only with file geodatabases.
Esri says it's working as designed:
Esri Case #03533443 - FGDB Select By Attributes — Subquery SELECT clause doesn't allow expressions
"Esri Inc has informed me that this behavior you're seeing is expected behavior. The nature of how subqueries work and the very limited nature of its implementation in FGDBs means that only those comparison operators and set functions outlined in the documentation are supported.
This explains why arithmetic operators, cast functions, string concatenation, and the like, fail validation in File Geodatabases. Using enterprise or mobile geodatabases is the suggested workaround."
Idea:
Could that behaviour be changed so that SQL expressions can be used in the SELECT clause of a subquery in Select By Attributes?
My example in this post is oversimplified to demonstrate the issue. My real use case is to select the greatest 1 per group (ties or no ties) using Select By Attributes.
Related question: FGDB Select By Attributes — Subquery SELECT clause doesn't allow expressions