FGDB Select By Attributes — Subquery SELECT clause doesn't allow expressions

Bud

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.

 

Why does a SQL expression in the SELECT clause of a subquery throw an error in Select By Attributes?

I don't have that problem in mobile or enterprise geodatabases, only file geodatabases.

My example in this post is oversimplified for the purpose of demonstrating the issue. My real use case is to use Select By Attributes to select the greatest 1 per group (ties or no ties).

Related idea: FGDB Select By Attributes — Expressions in subquery SELECT clause

species_records.xlsx

andy-lovell-2024.jpg

Bud

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-query-expressions.htm#GUID-F5320DB5-4E54-4D49-BF31-A58F5379ED9F

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."