idea Join to table with definition query — column name should get prefixed with table name (SQL expression with subquery) in ArcGIS Pro Ideas

Join to table with definition query — column name should get prefixed with table name (SQL expression with subquery)

Bud — Wed, 22 Mar 2023 13:54:22 GMT

ArcGIS Pro 3.0.3; mobile geodatabase.

I have a definition query on a standalone table:

OBJECTID IN (3, 5, 11)

In a FC, I create a join to the table with the definition query (join: ASSET_ID=ASSET_ID). The definition query gets applied to the joined FC, and the OBJECTID gets automatically prefixed with the table name:

main.RoadInspTable.OBJECTID IN (3, 5, 11)

That works as expected.

Next, I do the same thing, but this time using a more complex definition query, one that has a subquery:

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

Use case for the complex definition query: https://community.esri.com/t5/arcgis-pro-ideas/one-to-first-joins-control-what-related-record-is/idc-p/1239993/highlight/true#M22488

When I go to create the join, I use the Validate Join tool first. The join is valid.

But when I create the join, it fails:

Failed to refresh table. 
Error: 
Underlying DBMS error [ambiguous column name:
objectid] [Roads][STATE_ID = 0]

The problem is: ArcGIS Pro hasn't given OBJECTID a prefix in the joined FC's definition query.

How it is:

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

How it should be:

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

If I manually add the prefix to the joined FC's definition query, then it works as expected. The error goes away.

But it took some time to figure out why that was happening.

Since prefixes already get added to columns in simple definition queries, could the same thing happen for more complex definition queries? (ones with subqueries like above)

That would avoid some headache.

Thanks.

Re: Join to table with definition query — column name should get prefixed (SQL expression with subquery)

SSWoodward — Wed, 11 Jan 2023 23:56:45 GMT

Thanks for bringing this to our attention @Bud

I can reproduce your error, and after some discussion with the team, have decided this would be better marked with the Geoprocessing label. I've changed it and brought it to the attention of the correct team.

Thanks Again.

Re: Join to table with definition query — column name should get prefixed (SQL expression with subquery)

Bud — Mon, 06 Feb 2023 21:17:28 GMT

Screen recording video (ignore the video title; this video demonstrates multiple issues; I used it in multiple posts):

(view in My Videos)

Re: Join to table with definition query — column name should get prefixed (SQL expression with subquery)

Bud — Mon, 13 Feb 2023 15:01:19 GMT

Esri Canada Case #03258894
ArcGIS Pro 3.0.3 — Validate Join passes, but resulting join has error
BUG-000155819: Attribute table of joined layer in Mobile Geodatabase does not open when a definition query with a subquery is applied to the join table.
...The join will only determine if the join is valid, which in this case it is. There are matching records in both the input table and the join table. Ultimately the issue is with the attribute table itself. There is some misconfiguration in either the way Pro is sending the subquery nested within the join query or the way the database is interpreting the subquery.

Re: Join to table with definition query — column name should get prefixed with table name (SQL expression with subquery)

Bud — Wed, 22 Mar 2023 13:56:36 GMT

@KoryKramer Feel free to close, since support confirmed this is a bug.

Re: Join to table with definition query — column name should get prefixed with table name (SQL expression with subquery) - Status changed to: Closed

KoryKramer — Thu, 23 Mar 2023 18:17:48 GMT

Closed as a bug. Users experiencing this issue can follow https://support.esri.com/en-us/bug/the-attribute-table-of-a-joined-layer-in-a-mobile-geoda-bug-000155819 for status updates.

Re: Join to table with definition query — column name should get prefixed with table name (SQL expression with subquery)

Bud — Sat, 24 Feb 2024 16:15:12 GMT

Possibly related:

Article ID:000002957 - ArcMap

Features do not draw after joining data to a layer with a definition query

Re: Join to table with definition query — column name should get prefixed with table name (SQL expression with subquery)

JonathanNeal — Thu, 22 Aug 2024 18:45:11 GMT

@Bud Yes the above KB article is relevant for ArcMap. For Pro 3.2+, this has been fixed.