ArcGIS Pro 2.9.5; Oracle 18c 10.7.1 eGDB:
There are situations where the Add Join geoprocessing tool isn't suitable, such as with this issue:
BUG-000154798: The 'Select By Attributes' tool does not select only records with specified field val...
A query layer would be a suitable workaround for some use cases (instead of exporting the Add Join data to a FGDB).
select
cast(rownum as int) as unique_id, --cast as integer to ensure Pro knows the values are integers, not doubles
a.*,
b.objectid as join_objectid, --"join_' prefix: can't have duplicate field names
b.project_id as join_project_id,
b.fund_source as join_fund_source,
b.year_fund as join_year_fund,
b.amount as join_amount
from
projects a --one
left join
project_finances b --many
on a.project_id = b.project_id Creating a query layer from scratch might be feasible for SQL power users like me, if I'm not in a rush. But for non-SQL people doing fast-paced data analysis, writing SQL queries like that isn't practical.
So, I'm wondering if a geoprocessing tool could be added that would create a 1:M join query in a query layer without the user needing to write any code. It would be similar to the Make Aggregation Query Layer geoprocessing tool (screenshot), except it would be 1:M.
The query would need a calculated unique ID column for when the join is M:M (neither of the two table's ObjectID fields would be unique in that case). I think the unique ID column would need to be database-specific, such as using Oracle's ROWNUM pseudocolumn, or whatever mechanism is used by the database.
A tool/query like that would give users an alternative join mechanism when using enterprise geodatabase data, with the known limitation that the data wouldn't be editable, unlike Add Join (the input table is editable).
Could a Make Join Query Layer geoprocessing tool be added to ArcGIS Pro?
