Hello,
I have two datasets, 1. is a Enterprise Geodatabase Feature Class polygon layer. which is accessed via .sde connection, with read and write access.
the other 2. is a SQL table, which is accessed via a .sde connection, using a sql user with read only access.
I am joining the two datasets based on a common field. Both fields are Text, one is of length 10, the other 4000. An example of the data in each field is, 1. "BAGW01" 2. "BAGW01", they are identical.
As can be seen all items in table 2. match with something from dataset 1.
When the dataset 1 is viewed after this join, all of the fields from table 2 exist but are all NULL. What could be going wrong?
Edit: I've downloaded a local copy of the sql table to my local egdb, and joined that, that works as expected. But this isn't a solution, I need to be able to connect to the live sql data. Just thought id add this as it might give an insight into whats going on.
Edit 2: I've tried to join the table again a few times (after removing joins), now I can't open the polygon layer (dataset 1) at all, I get this error:
Assorted notes and questions:
(My experience is with Oracle, not SQL Server.)
A one-to-first join is also only possible if each table is from a different workspace.
1. ArcGIS Pro 3.1.2
2. I don't have anyone to ask at this point in time
3. Yes they are both tables held within different SQL dbs, version 13.0.6430.49
4. I would say it is not registered. How can I check this for certain? I know I do not have permissions to register it.
5. The IDs are unique in both of these tables. (although there are matches between the tables of course)
6 .it does not have an objectID column.
7. yes, unique values in the field I am using to join on
8. yes, unique values in the field I am using to join on
9.
10. Both are of field type text
11/12, through experimenting I am quite sure the length difference of the fields aren't causing problems. theyre both type text, and those same parameters are the same when joining the local export (table 2) with the data (1)
I think maybe the missing object_id might be causing an issue, the exported local version of the table (dataset 2) works and that has an object id field.
4. I think there are a few different ways you can check if a table is registered with the geodatabase or not. The way I do it is right click on the table in the Catalog pane. If the Register with Geodatabase option is shown in the right-click menu, then that means it's not registered. If that option is missing, then it is already registered.
Of course, if you know for sure that the underlying SQL Server database isn't a geodatabase, then that answers your question, too.
This table isn't registered with the geodatabase:
I think maybe the missing object_id might be causing an issue, the exported local version of the table (dataset 2) works and that has an object id field.
You could try creating a query layer in your APRX/Project on the second table. When creating a query layer, Pro will ask you what field is the unique ID field. That field will be treated as if it is an ObjectID field. You could try using the existing ID field in your table that has unique values as the unique ID field. If that doesn't work, you could try generating an integer field on the fly via SQL in the query layer. In Oracle, it'd be:
select
cast(rownum as int) as unique_id,
my_id,
...
The ROWNUM pseudocolumn is an Oracle-specific thing. So you'd need to figure out what the equivalent is in SQL Server.
Maybe Pro will behave better when joining to a query layer, compared to joining to a standalone table that isn't registered with a geodatabase (such tables are referred to as "database tables" instead of "geodatabase tables" in the Esri documentation).
How did it go?
Thanks for checking in!
I'm still working on this one, I wonder if it is something to do with missing an esri OID field?
In the meantime I've managed to get this to work using FME rather than ArcGIS Pro. Ill circle back and try and figure out why this happened in the near future.
In the meantime I've managed to get this to work using FME rather than ArcGIS Pro
Out of curiosity, what can you do with joined data/queries in FME? I assume you can’t add the data into Pro, other than maybe repurposing some SQL in a Pro query layer.
For example, do you export to Excel or some other format? What do you do with the export?
I ask because, as mentioned above, I too have a lot of issues with tabular/joined-base in analysis in Pro: Is ArcGIS Pro the right tool for tabular/join-based analysis?
I’m wondering if I should submit an I.T. project request for software like FME. My I.T. department is under-resourced, so it would likely take them ~10 years to get such software. So I’d want to make sure it was worth it.