Latest Contributions by Bud

Regarding BUG-000154798: The 'Select By Attributes' tool does not select only records with specified field values added through a one-to-many join but instead selects all records with duplicate ObjectIDs. (Status:  Known Limit) Select By Attributes on joined data — For rows that are 1:M, all rows in the join table get selected, despite selection criteria Is ArcGIS Pro the right tool for tabular/join-based geodatabase analysis? Could the various issues with 1:M (one-to-many) joins in Pro be solved by having a pseudo-ObjectID (row number) field as a unique ID? For example, Oracle's ROWNUM pseudo-column works fine for query layers as the unique ID. Could a similar row-number-based mechanism work for Add Join in the attribute table, too? select cast(rownum as int) as pseudo_objectid, lc.*, pf.* from lc_events lc --one left join lc_events_project_finances pf --many on lc.event_id = pf.event_id Edit: I think this approach would work for M:M (many-to-many) joins too. Related: Disallow 1:M join layers as GP inputs instead of producing unexpected results ... View more

When doing fast-paced data analysis using query layers, it's tedious to enter info like the query layer name each time I want to create a new query layer. Often, I'm just creating a temporary query layer in a blank map that I will delete shortly, so I don't need to put any thought into the name. Could a default name be generated for query layers so we don't have to enter a name each time we create a layer?   ... View more

In the New Query Layer window: It's cumbersome to choose the connection each time I make a query layer. Since I use the same .sde connection most of the time, can functionality be added to set a default connection? Or automatically select the last connection used, similar to SQL Developer. ... View more

Edited; removed FGDB-specifics in title since I already have an idea for that. In the Attribute Table: Could a could button be added for selecting rows where a specified field has duplicates? For example, select rows where the ASSET_ID field is duplicated. It would be similar to the functionality this SQL expression provides, except this expression doesn't work for file geodatabases: ERROR 000358: Invalid expression: asset_id in (select asset_id from sidewalks group by asset_id having count(*) > 1)  Due to that FGDB SQL limitation, I imagine the functionality would need to be ArcObjects behind the scenes, not SQL. I'm aware of the Find Identical tool. That tool exports a new dataset. I want to dynamically select in my existing dataset. Related: Select By Attributes — Select Duplicates ... View more

When users have problems with query layers, 9 times out of 10, the issue is with the Unique ID field. There are usually duplicates in that field that the user was unaware of. It would be great to have a button in the query layer Contents properties that checks for duplicates in the Unique ID column in the underlying data (using the raw SQL query). Not to be confused with checking for duplicates in the attribute table/feature layer (idea: Select By Attributes — Select Duplicates), which is not a true representation of the underlying data. If there are duplicates in the Unique ID field, then the duplicate rows will likely be silently/automatically omitted from the attribute table/feature layer, so checking for dups in the attribute table/feature layer wouldn't help us. We need to check for dups in the underlying data. Could a "Check for Duplicate IDs" button be added to the query layer properties? ... View more

In the Select By Attributes window: Could an option could be added to the operator picklist: "is duplicate"? I'm aware of the Find Identical tool. That tool exports a new dataset. I want to dynamically select in my existing dataset. ... View more

It would be great to have an OOTB geoprocessing tool that could create a query layer that is a pivot table/query. The processing would be done dynamically by the database, not the application, just like other query layers. Similar to the Make Aggregation Query Layer tool. Related:  Pivot Table Chart Style in ArcGIS Pro Make Join Query Layer geoprocessing tool ... View more

ArcGIS Pro 2.9.5; Oracle 18c 10.7.1 EGDB: A Selection Layer's list of features is stored in a Selection Definition Set: The list of features is hidden. Instead of obscuring the list of features in a Selection Definition Set, could the list of features be stored in a definition query expression based on the ObjectID? OBJECTID IN (1, 2, 3, 4, 5, 6, 7, 8, 9) Alternatively, could the list of features be displayed in a copyable text box in the Properties > Selection tab? For use in SQL queries, documentation, etc. This idea is similar to a point I made in Consistent/visible SQL expressions and query definitions in layers. But I felt it warranted its own separate idea. (I thought there was an existing idea for this, but I couldn't find it.) Related: Selection to Definition Query ... View more

ArcGIS Pro 2.9.5; Oracle 18c 10.7.1 EGDB: I've created a query layer using the Make Aggregation Query Layer tool. This is what the SQL of the resulting query layer looks like: I've formatted the SQL for easy viewing: select t.*, row_count from infrastr.strln_route t inner join (select asset_id, count(*) as row_count from infrastr.lc_events group by asset_id ) s on t.rdsec = s.asset_id I can imagine that the FC is aliased as  t  for "target feature class". But why is the table aliased as  s  ? What does "s" stand for? Could those aliases be improved so that they're more intuitive? For example: FC for Feature Class; TBL for Table. Or something better ... View more