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

463
7
12-21-2022 12:45 PM
Status:Needs Clarification
Labels(1)
Bud
by
Frequent Contributor

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?

7 Comments
JoshuaBixby

Esri has a bigger problem with that documentation you reference because ArcObjects has nothing to do with ArcGIS Pro. According toArcObjects 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]." TheArcGIS Pro SDK for .NETis 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
Status changed to:Needs Clarification

Thanks for the Idea,@Bud.

这是我的理解,所有的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
by

@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:

//www.gobook3.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_idORDER 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 ifSQL for reporting and analysis on file geodatabasesactually 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
by

@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

Bud_0-1673896763393.png

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
by

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.

Bud_0-1674010352809.png

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