Duplicate objectid in Views - Best practise to solve?

[AndersMark]

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. 

[jcarlson]

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.

[AndersMark]

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?

[Alvaro1977]

i have de same problem, you can resolve de issue 

[Bud]

@Alvaro1977 

What kind of database? Oracle? 

If not Oracle, then does your RDBMS have the equivalent of Oracle's ROWNUM pseudocolumn? 

[Alvaro1977]

yes, i use rownum in the query . 

select cast(rownum as number(38.0)) as objecid,
d.shape,

[AndersMark]

See first row - "I've run into a problem with an Oracle View..." so yes, it's Oracle. 

[Bud]

I've had success using Oracle's ROWNUM pseudocolumn as a fake/unique ObjectID when there are duplicate ObjectIDs in the query.

select
    cast(rownum as int) as rownum_,
    r.*
from
    my_user.roads r

But a couple of extra steps are required:

1. ArcGIS doesn't seem to recognize the ROWNUM values as integers. So I need to cast it as an int.
   1. Alternatively, cast it like this: cast(rownum as number(38,0)) as rownum_
2. When it comes to creating a view, Oracle doesn't like it when I name the column as ROWNUM, since that's a reserve word. I need to name it ROWNUM_ with an underscore (or something similar), or name it as OBJECTID.

Related: Converting double field to integer in database view so ArcMap query layer can use field as unique id...

[Alvaro1977]

Yes, it worked,