Latest Contributions by Bud

Add Join (Data Management) Optionally, set the Join Operation parameter to Join one to first to prevent duplicate object IDs. The Join Operation parameter has three states to adjust the cardinality. The default is blank and will allow the data source to attempt a one-to-many join. The Join one to many option will work only on specific data sources that have an Object ID field. The Join one to first option will use the first match in the table, which may result in different outputs if the Object ID field is changed or the workspace the table is copied to changes. One-to-first joins are not case sensitive; one-to-many joins are case sensitive.   ... View more

@MargaretCrawford  Yes, specifying a FC name worked. Thanks. Bud_0-1715210571125.png Or, at least, I think it would have worked under normal circumstances. I have a known Shape Integrity Error issue with this FC. Bud_1-1715210670404.png Esri Support Case: 03623136 - Copy GP tool: Misleading error message when only the GDB path used in Output Data parameter ERROR 000979 Cannot copy between different workspaces. That error message is misleading. The problem is the FC name is missing. It has nothing to do with copying data to a different workspace. Is that error message a bug? ... View more

Edit: Never mind. The syntax is the same as your query. As a last resort, you could try using the Select Layer by Location — Intersect (DBMS) geoprocessing tool which creates a spatial SQL query in a database view. Maybe the syntax or performance would be different. You could steal the SQL from the view to use in your query. Related: Select Layer by Location — Intersect (DBMS) — Existing Selections Intended use case for Select Layer by Location — Intersect (DBMS)? Select Layer By Location — Indicate that Intersect (DBMS) creates a database view Select Layer by Location — Intersect (DBMS) — User-defined output database view name Select Layer by Location — Intersect (DBMS) — Create a query layer instead of a database view Select Layer by Location — Intersect (DBMS) — Show SQL query definition in GP tool details ... View more

@MargaretCrawford  Interestingly, if there is a selection in a Make Query Table that has a SQL expression, and I disable the selection toggle in a tool like Export Features, the tool doesn't ignore the SQL expression in the Make Query Table layer. I guess that's because the SQL expression in Make Query Table layers is hidden/embedded within the layer; it isn't a definition query. https://community.esri.com/t5/arcgis-pro-ideas/consistent-visible-sql-expressions-and-query/idc-p/1366709/highlight/true#M27874 Bud_0-1715137833409.png I'm not suggesting that's good or bad. Although, from a purist standpoint, I would have considered an SQL expression in a Make Query Table layer to be an attribute filter. But it isn't being treated as an attribute filter in this case. Video: ArcGIS Pro 3.3.0; File Geodatabase   Edit: Or maybe the toggle just doesn't work at all for the Make Query Table tool. Maybe it doesn't do anything, regardless if it’s enabled or disabled, as suggested in my previous comment. ... View more

New Geodatabase Features Add Functionality But Complicate Compatibility and Interoperability ... View more

Somewhat related: Geodatabase mechanisms that make FCs unusable in ArcMap ... View more

Out of curiosity: For organizations who might be doing a GIS upgrade project in the next 6-9 months: If Oracle 23ai on-prem hasn't been released at the time of the upgrade, or if ArcGIS Enterprise doesn't support 23ai at the time of the upgrade, is there a risk that the new ArcGIS Enterprise implementation would be forced to use 19c, which will only be supported until 2026? For organizations who only have resources to upgrade their GIS every 5-6 years, would they be caught in an awkward spot where they’re using 19c, which would be unsupported shortly after implementation? This isn't my area of expertise. ... View more

If you don't get an answer, you could post it as an idea. ... View more

For future reference, the related post: ArcGIS Pro Data Design Field Alias From Contents or Catalog Not the Same ... View more

For future reference, your related post: Warn user when configuring fields via layer list ... View more

It turns out the issue doesn't only apply to Select By Attributes. It also applies to manual selections. With a 1:m join, if I manually select one of the rows that is 1:M, then all rows in the 1:M group will get selected, which is incorrect. Video (using different sample data): ... View more

Since this issue is specific to 1:M joins, a possible workaround is to make the join 1:1 instead of 1:M: Use the Make Query Table tool to hide the OBJECTID field in the join table. Join tables that don't have an OBJECTID field are automatically treated as 1:1 (one-to-first) joins. https://community.esri.com/t5/arcgis-pro-ideas/choose-if-join-will-be-one-to-first-or-1-many/idc-p/1368759/highlight/true#M27968 Or, possibly join to a query layer or an unregistered database view, since I assume a join to an unregistered item will be one-to-first, similar to joining to an unregistered table: https://community.esri.com/t5/arcgis-pro-ideas/1-m-join-using-unregistered-tables-query-layers/idi-p/1473535  Alternatively, ArcGIS Pro 3.3.0 has an OOTB option to choose if the join will be 1:1 or 1:M. So in 3.3+, choose 1:1 when making the join. https://community.esri.com/t5/arcgis-pro-ideas/choose-if-join-will-be-one-to-first-or-1-many/idc-p/1407322/highlight/true#M29286    Or, to control what related record gets used in a 1:1 join, use this as the SQL expression in a Make Query Table (screenshot) : table_b.objectid IN (SELECT objectid FROM table_b t2 WHERE t2.b_id = table_b.B_id ORDER BY b_text_field ASC FETCH FIRST ROW ONLY) SQL expression source: Join — Control what related record gets used The Make Query Table should include the OBJECTID field, since the SQL expression above references the OBJECTID. Then, join to the Make Query Table. The benefit of this approach is that it avoids issues with joining to a table that has a definition query, such as Add Join not keeping all target features and other bugs. The Make Query Table hides/embeds the SQL expression within the layer (it doesn't create a definition query), so that's how the usual "joining to a table with definition query" issues are avoided. Also, as mentioned in a previous comment, definition queries don't have this problem. The problem is specific to selections. So in some cases, maybe a definition query could be used instead of a selection. Other alternatives: Do the join using SQL in a query layer or database view (the input table won't be editable, unlike Add Join). Ensure there is a truly unique ID field, possibly by using something like Oracle’s ROWNUM pseudo column, or the equivalent in your database. Or, pare down the related table in a query layer/database view, so that joining to the query layer/database view is a 1:1 join. Editing the input table will be possible. But the join will be slow for medium to large datasets. Edit: With that said, performance might have improved in 3.3.0: "The geoprocessing framework uses workspace caching to improve performance when running scripts or repeated processes. This improvement applies particularly to remote data sources, such as enterprise geodatabases." Or, export the data to the same enterprise geodatabase, or to a file geodatabase or mobile geodatabase. Do the selections there. Or, for standalone tables, switch from joining from TABLE_A to TABLE_B, to joining from TABLE_B to TABLE_A. That might change the join from being 1:M to M:1 (1:1), which would eliminate the selection issue. But editing might be a problem, since only the input table can be edited in a join, not the join table. ... View more