Relate from table to table joined to query layer

[Bud]

ArcGIS Pro 3.2.1; Oracle 18c:

Background:

I have a ROLL_UP table that has a COMPOSITE_ID field.

And I have a PROJECTS table:

I want to do a relate from the ROLL_UP table to the PROJECTS table via the COMPOSITE_ID field.

Since PROJECTS doesn't have a COMPOSITE_ID field, I need to create one by concatenating EVENT_STATUS and YEAR_CON.

• Ideally, I'd use a mechanism like Virtual Attributes. But that functionality doesn't exist.
• I don't want to use a calculation attribute rule because I don't want to clutter up the table with an additional permanent field — just for this one data analysis task. Also, the table is locked.
• I need to edit the data in PROJECTS. So query layers and database views aren’t suitable, at least not on their own.
• So instead, I'll create a query layer and join from the PROJECTS table to the query layer.

Create a query layer on PROJECTS:

select
    objectid,
    event_status||year_con as composite_id
from
    projects

Join from PROJECTS to the query layer via the common OBJECITD fields.

Now, I have a calculated COMPOSITE_ID field in PROJECTS.

Idea:

I want to do a relate from the ROLLUP table to the PROJECTS table (and the query layer) — using the common COMPOSITE_ID fields.

I can create the relate without errors.

However, the relate doesn't work as expected. If I select a record in ROLL_UP and use Related Data to select the related records in PROJECTS, then no related records in PROJECTS get selected.

Whereas, I don't have that problem if I export PROJECTS (and the joined query layer field) to a static table called PROJECTS_STATIC_EXPORT. And then do the relate from ROLL_UP to PROJECTS_STATIC_EXPORT. The relate works without issue, proving that the logic is sound.

But I don't want to create a copy/export of PROJECTS. I want to use live PROJECTS data, including editing the data.

Video:

Could ArcGIS Pro be enhanced so that we can relate from a table to a table joined to query layer?

Similar post: Relate using multiple fields

[MarceloMarques]

I understand that you want to avoid to add a new field, but sometimes this is the best solution for performance and data model normalization, you can add the composite_id column to the projects table, then populate the composite_id = event_status || year_con, then create an index on composite_id, next use ArcGIS Pro Attribute Rules with arcade expression to populate the composite_id automatically when a new record is inserted or when a record is updated, and consider to make the fields event_status, year_con as not null, then, you can create a geodatabase relationship class between the projects table and the roll_up table.

[Bud]

I think I figured it out. The problem seems to be when the query layer has spaces in the name. If I create a query layer with no spaces in the name, the relate works as expected.

• "query layer" doesn't work.
• "query_layer" works.

Video:

Esri Case #03543479 - Relate from table to table joined to query layer

BUG-000165217 - When a relate exists between a table and a query layer with a composite key joined to another table, related records are not displayed if the query layer name contains a space.

I figured it out by testing a database view instead of a query layer. I didn't have the issue with the view since views don't allow spaces in the name. So, from there, I determined that the space in the query layer name was the issue.

[Bud]

If the ROLL_UP table were acually a view, then the following would apply:

In Pro 2.6.8, I had to register the database view with the geodatabase to get it to work. Otherwise, it was very buggy. Random rows would get selected in the view when I tried to point to the related records, which was incorrect. (Oracle 18c 10.7.1 EGDB)

I don't have that problem in ArcGIS Pro 3.2.2 with a non-gdb Oracle 18c Express environment (when the view isn't registered with the geodatabase).

[Bud]

As @MarceloMarques suggested, the performance of this approach is poor. It takes 15 seconds to do anything in the attribute table of the PROJECTS table, such as relate to PROJECTS records or sort the attribute table. There are only 800 records in PROJECTS, so performance really shouldn't be a problem.

The reason for the poor performance is likely this: Perform join at the database level, not the application level, when the datasets are in the same wor...

I don't have this issue when doing the equivalent join in pure SQL. It's only a problem in ArcGIS Pro.

And as mentioned in the above idea, ArcGIS Pro does the join correctly for mobile geodatabases (joined to a database view, since MGDBs don't have query layers); the join is done at the database level, not the application level. Whereas with EGDBs, Pro seems to do the join at the application level, causing slow performance.

BUG-000160320:  Slow performance in Pro when interacting with the attribute table of data which participates in a join.

[Tatag_BagusNarendra]

does it give the same result using pandas?

import arcpy
import pandas as pd
pd1 = pd.read_excel('D:/temp/_test/PROJECTS.xlsx')
pd2 = pd.read_excel('D:/temp/_test/ROLL_UP.xlsx')
merged_df = pd.merge(pd1, pd2, on=['EVENT_STATUS','YEAR_CON'], how='left')
print(merged_df)
file_name = 'D:/temp/_test/EXPORT_TEST.xlsx'
merged_df.to_excel(file_name)