Latest Contributions by Bud

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: Bud_0-1715088675883.png 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).   ... View more

I'm starting to wonder if my original subquery approach was flawed: Oracle (FETCH): roadinsptable.objectid IN ( SELECT objectid FROM roadinsptable r2 WHERE r2.asset_id = roadinsptable.asset_id ORDER BY date_ DESC, condition DESC FETCH FIRST ROW ONLY ) Reason: In the join, Pro uses the subquery to pare down the entire query, not just pare down the join table. More info here: Add Join — Create independent definition query on join table (pre-filter) ... View more

If you're having trouble with the SQL, I would suggest copying the data to a geodatabase type that supports SQL clients, such as a mobile or file geodatabase: https://community.esri.com/t5/geodatabase-questions/select-maximum-values-in-select-by-attributes/m-p/1369748/highlight/true#M8761 You might ultimately find that file geodatabase SQL is too buggy/problematic. ... View more

Ontario Elevation Mapping Program: https://geohub.lio.gov.on.ca/pages/ontario-elevation-mapping-program  https://geohub.lio.gov.on.ca/maps/mnrf::ontario-digital-terrain-model-lidar-derived/about   ... View more

Assorted notes and questions: (My experience is with Oracle, not SQL Server.) What version of ArcGIS Pro are you using? Each version of Pro seems to have its own set of join-related issues. Does anyone else at your organization have a different version of Pro you could test with? Or ArcMap? Just to clarify, are both tables SQL Server tables? What version of SQL Server? And is the second table NOT registered with a geodatabase? For what it’s worth, I suspect the join is one-to-one (one-to-first) because the tables aren’t in the same workspace. If they were in the same workspace, the join would be one-to-many. Add Join (Data Management) A one-to-first join is also only possible if each table is from a different workspace. Bud_1-1714988356921.png Does the second table have an ObjectId column? I suspect no, if it is not registered with a geodatabase. Does the join field in the first table have unique values? Can you check via an SQL query or some other way? Does the join field in the second table have unique values? Can you check via an SQL query or some other way? Related: Is ArcGIS Pro the right tool for tabular/join-based analysis? In the Catalog properties of each table, is Pro treating the field datatypes the same? Both as TEXT, but with different lengths? Or is one field treated as some other datatype, such as the SQL Server equivalent to Oracle’s BLOB or CLOB? "The name of the field does not have to be the same, but the data type must be the same; you join numbers to numbers, strings to strings, and so on." Introduction to joins and relates And what are the underlying datatypes in the SQL Server databases? I.e., what does a SQL client like Toad or SSRS show the datatypes as? In your temporary copy of the second table in the EGDB, what is the join field’s datatype in the Catalog properties? Can you create a database view or query layer on the second table, where you’d CAST the join field from length=4000 to length=10? And join using the database view or query layer instead of the table. So that your join fields both have the same lengths? Or at least similar lengths? Does the join need to allow editing on the first table? Or would a read-only workaround be suitable? For example, could you get your DBA to make the equivalent of an Oracle DBLINK, but in SQL Server, that would let you create a query layer on both tables using only one .sde connection? Does making join using Make Query Table work as expected? https://community.esri.com/t5/arcgis-pro-questions/how-to-join-using-make-query-table/m-p/1392162 That mechanism is pretty clunky, so it’d only be a very last resort. Are you able to submit a case to Esri Support? If you don’t get an answer here, you could try asking a moderator to move this question to the Data Management Community (via the Report Inappropriate Content button in the post’s options). There are some SQL Server experts over there. As you’ve hinted, it’s strange that the messages in the screenshot indicate that matches have been found for 137 records, yet the attribute table is not showing any matches. Regarding the error in Edit 2: I’ve had similar issues when a join is broken in Pro due to bugs. If it’s a throw-away APRX/Project, then I sometimes have luck using the Remove All Joins option in the attribute table. If not, then I discard my changes to the Project or create a new Project. Join had no matches but two attribute fields have same name ... View more

Add Join (Data Management) Bud_1-1714847199906.png The second table uses a join table with no Object ID field; only a one-to-first join is possible. A one-to-first join is also only possible if each table is from a different workspace. A one-to-first join is not a case sensitive match. Records from the join table can be matched to more than one record if the join table has an Object ID field; otherwise, a one-to-first join will be performed. ... View more

I cannot get ArcGIS Pro to do a 1:M join between a Feature Class and a table in a FGDB. It definitely should be a 1:M situation, but it insists on trying to do 1:1 and therefor records are lost. ArcMap does the 1:m join just fine with the same FGDB. What tool are you using to create the join? Add Join? My understanding is that all joins in ArcMap are one-to-first (1:1). I don't understand how it could be possible that ArcMap is doing a 1:M join. Does the join work as expected when done outside of a model in the attribute table? ... View more

Possibly related: Selecting maximum values of a field, by group ... View more

Possibly related: Selecting maximum values of a field, by group ... View more

@YusefSamari What kind of geodatabase is the data stored in? Can you attach sample Excel data (or whatever works, such as a zipped file geodarabase or zipped mobile geodatabase) to this post? ... View more

What geoprocessing tool are you using? Possibly related:  BUG-000166125: ArcGIS Pro 3.2.2 - Make Query Table Tool joining all tables despite selected records ... View more