FGDB Select By Attributes — Expressions in subquery SELECT clause

Bud — Tue, 30 Jan 2024 01:10:09 GMT

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.
https://pro.arcgis.com/en/pro-app/latest/help/mapping/navigation/sql-reference-for-elements-used-in-...
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.

idea FGDB Select By Attributes — Expressions in subquery SELECT clause in ArcGIS Pro Ideas

FGDB Select By Attributes — Expressions in subquery SELECT clause