topic Re: Join to table with definition query — removing join breaks the layer in ArcGIS Pro Questions

Join to table with definition query — removing join breaks the layer

Bud — Tue, 08 Aug 2023 19:38:18 GMT

ArcGIS Pro 3.0.3; mobile geodatabase:

I have a FC and a standalone table:

I apply a definition query to the table -- to pare-down the rows so that only the latest inspection per asset is shown:

date_ = (select max(subq.date_) from roadinsptable subq where roadinsptable.asset_id = subq.asset_id)

I create a join from the FC to the table:

The join works as expected. And the definition query from the table is honoured.

Later, I change my mind: I've determined I don't need the join, so I remove it:

Problem:

Removing the join causes an error:

Failed to open table. 
Error:
Attribute column not found [no such column: date_]
[Roads][STATE_ID = 0]

I hit OK. The attribute table of the FC automatically closes.

I try to re-open the attribute table of the FC:

But I get the same error I saw previously. And the attribute table doesn't open.

Trying again produces the same error.

The only way to successfully open the attribute table again is to remove the FC from the map and re-add it, which isn’t ideal.

Is that a known issue?

I only have the problem when the join table has a definition query. If there's no definition query, then I can remove the join without getting the error.

Re: Join to table with definition query — removing join breaks the layer

AlfredBaldenweck — Mon, 12 Dec 2022 13:53:46 GMT

I have also run into this

Re: Join to table with definition query — removing join breaks the layer

eclecticlearner — Tue, 08 Aug 2023 17:42:34 GMT

ArcGIS Pro 3.1
I've run into a VERY similar issue (the same error message). My scenario is this:
Add a sqlite table to a map
Add a featureclass to the same map
Add a definition query on the sqlite table to limit the number of records
Join the featureclass (id field integer) to the sqlite table (id field integer)

The join works just fine, but when opening the attribute table of the featureclass (which is now joined to the sqlite table) Same error

"failed to open table.
Error:
Attribute column not found [no such column: 'name-of-the-first-column-used-in-the-sqlite-table-definition-query']

Re: Join to table with definition query — removing join breaks the layer

Bud — Tue, 08 Aug 2023 20:34:15 GMT

@JonathanNeal might be interested in this.

Edit:

I tested in 3.1.2 just now and am still seeing the issue in that version.

I submitted an Esri Canada case this afternoon:
03410020 - ArcGIS Pro 3.1.2: Join to table with definition query — removing join breaks the layer

Re: Join to table with definition query — removing join breaks the layer

eclecticlearner — Wed, 09 Aug 2023 14:30:22 GMT

Thanks for replicating this issue @Bud . I am also using ArcGIS Pro 3.1.2.

I believe you mentioned @JonathanNeal because of this post.

"Two likely scenarios come to mind in 3.1- and in ArcMap that you can mark as a YES:
1. Subqueries do not work if done before the join on datasources with "." "

The response by him strikes me as what could be the issue. If ArcGIS Pro adds (as default behavior) sqlite/gpkg tables as a "query layer" then would a definition query on that query layer be treated as a subquery? For reference I just drag and drop the tables into the map, sometimes I it asks for me to identify the uniqueID column but otherwise I've treated them no different than any other table when adding/removing etc.

All of my sqlite tables show up in catalog pane as "main.mytablename". For the life of me I have not figured out how to implement this in such a way that ArcPro drops the "main." It would be great if I could solve that part of the equation and see if it solves the definition query part of the equation.

Anyhow, thanks for the reply and hopefully this post gets some traction. I have a tool I'm ready to implement but an error like this has me debating on rewriting the entire tool to output in some other way (I need outputs to be able to be read by more than just ESRI applications).

Re: Join to table with definition query — removing join breaks the layer

Bud — Wed, 09 Aug 2023 20:40:17 GMT

Interesting.

Even if Esri doesn't plan to fix this issue: Subqueries do not work if done before the join on datasources with "." , at the very least, it shouldn't permanently break the layer (the join can't be removed). That should be fixed. And there should be helpful error messages to tell us what the problem is.

As I mentioned in a different post, the measure of software quality is how good the error messages are.

Re: Join to table with definition query — removing join breaks the layer

Bud — Sun, 13 Aug 2023 21:59:33 GMT

From Esri Support in case #03410020 (open):

I have followed the workflow you provided and was able to reproduce the issue you encountered. I'd like to share a few observations with you:
Apart from removing the FC from the map and then re-adding it, I found that I could open the attribute table again by removing the definition query in the FC that inherited from the definition query in the table
This is an alternative approach to successfully opening the attribute table again
I attempted to apply a definition query in the default mode instead of the SQL mode and encountered no errors
Upon further research on the "Attribute column not found" error, it seems that a possible cause is the QueryDef object is not handling wild cards (*) for its SubFields property if there are fully qualified fields in any of the tables (for example AREA).
You can explore potential workarounds for this issue at https://support.esri.com/en-us/knowledge-base/error-attribute-column-not-found-000003383

Re: Join to table with definition query — removing join breaks the layer

Bud — Wed, 30 Aug 2023 14:27:34 GMT

Esri Support reported the following bug for me:

BUG-000160924 : SQL query becomes case sensitive when a Feature Layer is joined to a table stored in a Mobile Geodatabase. After joining a feature layer and a table (both stored in a Mobile Geodatabase), SQL query becomes case sensitive and the previous SQL query expression fails to execute.

@JoshuaBixby might find this interesting.