SQL syntax in FGDB API should be supported in ArcGIS Pro FGDB queries

[Bud]

If I understand correctly, the SQL syntax in the FGDB API (see: SQL for reporting and analysis on file geodatabases) is only partially supported in FGDB views and expressions.

It seems weird to me that there would be SQL syntax that is available to the FGDB API, but not available to SQL queries in ArcGIS Pro (FGDB views and SQL expressions).

Could SQL queries in Pro be enhanced so that they fully support the more advanced SQL syntax in the FGDB API?

[JoshuaBixby]

Esri has a bigger problem with that documentation you reference because ArcObjects has nothing to do with ArcGIS Pro.  According to ArcObjects Help for .NET developers (ArcObjects .NET 10.8 SDK), "ArcObjects is a library of Component Object Model (COM) components that make up the foundation of ArcGIS [Desktop/ArcMap]."  The ArcGIS Pro SDK for .NET is a completely different SDK than ArcObjects SDK, and there is a chance the code examples don't even transfer over.  It looks like Esri got sloppy and just fork-lifted the Desktop document over as Pro documentation.

[SSWoodward]

Thanks for the Idea, @Bud.

It's my understanding that all of the SQL currently supported in the FGDB API is also supported in Pro. To help us better understand the limitations you are running up against, would you be able to share some SQL statements used in your workflows that are not supported in ArcGIS Pro? 

 

[Bud]

@SSWoodward 

Regarding, SQL for reporting and analysis on file geodatabases, I don’t think these SQL keywords work in FGDB definition queries/subqueries:

• CASE
• COALESCE
• JOIN
• NULLIF
• ORDER BY

Here’s an example of something I want to do in a FGDB definition query, but can’t, due to FGDB SQL limitations:

https://community.esri.com/t5/arcgis-pro-ideas/one-to-first-joins-control-what-related-record-is/idc...

Works for SQLite/mobile geodatabases, but not file geodatabases:

roadinsptable.objectid IN (
     SELECT objectid
       FROM roadinsptable r2
      WHERE r2.asset_id = roadinsptable.asset_id
   ORDER BY date_ DESC, condition DESC
      LIMIT 1
            )

I’m aware that FGDB SQL doesn’t have LIMIT, not even in the FGDB API. There are likely other ways to accomplish it using SQL, without LIMIT, in databases that have full SQL support. But those alternatives wouldn’t be possible in FGDB definition queries due to the limited FGDB SQL.

Part of the problem is that I’m not actually sure if  SQL for reporting and analysis on file geodatabases actually pertains to the FGDB API. It’s a confusing page in a confusing place with no context.

Edit:

Here's another example of the useful stuff we can do in geodatabases like enterprise and mobile, but not in file geodatabases: Force bar chart to show missing years within 10-year range (system year + 9). The queries generate filler rows so that there are rows/bars for each year in the 10-year range.

[JoshuaBixby]

@Bud , regarding your specific query (excluding the LIMIT aspect of it), the query doesn't work because of limitations with SQL joins with file geodatabases.  At least for the File Geodatabase API, Esri has always listed 2 known issues: GitHub - Esri/file-geodatabase-api: ... The File Geodatabase C++ API for Windows, MacOS and Linux

Known Issues

• Concurrent access from Windows and Linux clients to the same File GeoDatabase can corrupt data. This combination should be avoided.
• SQL joins are not supported.

It is likely that limitation in the FGDB API isn't just an FGDB API limitation, i.e., file geodatabases overall do not support SQL joins.  Is a bullet point on a GitHub page sufficient documentation?  I don't think so, I am guessing you don't think so, but it seems Esri does think so since they haven't materially improved their FGDB SQL documentation for many years.

[Bud]

@JoshuaBixby 

Meanwhile, SQL joins appear to be supported in FGDB views:

select 
    *
from
    table_a a
inner join
    table_b b
    on a.id_a = b.id_b

ArcGIS Pro 3.0.3

[JoshuaBixby]

I just tried your example on Pro 3.0.3, and I get "An invalid SQL statement was used" error.  I am able to create the tables and create the view, but I can't add it to a map.  Are you able to script out your steps so I can try running the code exactly the same way because something in our workflows is obviously different if it works for you but I get an error.

[Bud]

Edited.

It seems there is a bug in ArcGIS Pro 3.0.3.

I tried to repeat my steps, but I got the same error you did when adding the view to the map:

Failed to add data, unsupported data type.

An invalid SQL statement was used.

Workaround:

Create the tables in a FGDB, but then copy the tables to a new FGDB (using right-click copy/paste in Catalog). In the new FGDB, I'm able to create the view, add the view to the map, and open the attribute table.

Does that workaround work for you?

Note: The bug doesn't appear to be specific to the INNER JOIN view we're testing. I got the same error when creating a simpler view: 

select 
    *
from
    table_a

Related: Bug: View on FGDB standalone table — can't add view to map

[Bud]

@SSWoodward 

This idea is out of date and can be closed. I think most of the issues that were brought up in this post are covered in other, more specific ideas, ENHs, and bug reports.