I've run into a problem with an Oracle View that produces duplicates of column OBJECTID.
The underlying geo-table has of course unique objectid:s but in the view, every geometry can occur severeal times. This violates functionality that requires that the ObjectID be unique. https://desktop.arcgis.com/en/arcmap/latest/manage-data/using-sql-with-gdbs/object-id.htm
The view is a registred view in ArcGIS.
In my case I have a ArcMap service that consumes the view and when duplicate objectids are spotted it aggregates all rows with the same objectid. Maybe thats fine with the geometrys, as many lines can exist in a poly line field. The problem is the attributes where only one of aggregated rows will represent the geometry/row when consumed in the map service. Maybe it's the first row of the aggregated records that will display in the map or one of the others, it's hard to tell. It feels random.
Is there a best practise to solve this kind of issue?
I have tried to make the view generate it's own unique value in the OBJECTID column instead reading that's written in the underlying table but when I do this it's not possible to register the view in ArcGIS.
I would recommend using SELECT DISTINCT together with ORDER BY to control the order in which your duplicates appear, if you need to consistently choose one result. Can you share the full query, though? If you're aggregating everything properly, there shouldn't be any duplicates.
I will try to explain:
I can't use distinct because every line in the view are correct which means it wouldn't be correct to use just one of the duplicate objectid:s. I.e. data would be missing. The view joins several tables inlucing one geometry table. The same geometry kan be used from more that one row in the view. This is the reason for duplicate object ids. The problem is that ArcGIS depends on that every geotable or geoview contains unique values for OBJECTID. I a view doesn't fulfill that need, ArcGis will automatically aggregate duplicate objectid:s and create a multipart feature but with attribute values from just one of the aggregated records. When checking information about one of those geometries you will see every geometry but only attribute values for one of them. This leads to lack of information which is not preferred.
Is the problem more clear now?