Add and Populate OBJECTID Field to SQL View in ArcCatalog

[KathrynWesson]

Hi everyone! I could really use some help with this, because I have no idea what I'm doing.

Basically, I'm trying to create a SQL View in ArcCatalog that references four SDE feature classes of various pavement types. They were published as a feature service through ArcGIS Pro to our organization's Portal (e.g. they are hosted on our own servers, but I am not the author of the data) for multiple people to survey in Field Maps. I therefore want the View to be linked in such a way that it automatically "refreshes" to reflect any updates that are made to the parent feature classes.

I was able to create a SQL View through ArcCatalog with the command UNION ALL to grab all records from the parent feature classes. Initially, the total number of records in the View matched the total number of records from all four parent feature classes. However, I noticed today that the View now has 1,241 more records than before. I'm quite sure no one but myself is looking at/making changes to the parent data, and all I've done is delete a single record from one of the parent feature classes to test that updates are translating. So, I'm not sure where this disparity is coming from.

I wonder if I need to "Register with Geodatabase" to get my View to sync up properly. Apparently it needs an OBJECTID field with unique values. Here's where I run into issues; each parent feature class has its own OBJECTID field that starts with 1, so when I run the UNION ALL command on them, the OBJECTID field has duplicate values. How would I go about adding and populating an OBJECTID field with unique and sequential values to my SQL View through ArcCatalog?

Here is what I have so far:

The highlighted items in the Catalog tree are the four parent feature classes. The first portion of the View Definition seemingly works, but I'm struggling with the last two lines of the command (concerning the new field). How would I adjust my View Definition to add and populate this new unique identifier field?

Any help would be greatly appreciated!

ArcGIS Desktop 10.8.1 - ArcGIS Pro 2.6.1 - Portal for ArcGIS 10.8.1 (production server) - Portal for ArcGIS 10.9.1 (test sever)

[TanuHoque · Accepted solution]

@KathrynWesson 

There is one other way (i.e. Window function) that I know you can generate unique ids. That said, I could keep it simple and stupid by simply incrementing oids from each table by some offset number to keep numbers unique in this view.

(SELECT OBJECTID, SURV_DATE, SURV_CREW, EXEMPT_COD FROM TR.TR_GIS_ADMIN.CRCP
UNION ALL
SELECT OBJECTID + 1000000 as OBJECTID, SURV_DATE, SURV_CREW, EXEMPT_COD FROM TR.TR_GIS_ADMIN.Flexible
UNION ALL
SELECT OBJECTID + 2000000 as OBJECTID, SURV_DATE, SURV_CREW, EXEMPT_COD FROM TR.TR_GIS_ADMIN.Gravel 
UNION ALL
SaECT OBJECTID + 3000000 as OBJECTID, SURV_DATE, SURV_CREW, EXEMPT_COD FROM TR.TR_GIS_ADMIN.Rigid

[KathrynWesson]

So simple, and yet, I didn't even think to do this! Thank you. It worked like a charm!

[TanuHoque]

I'm glad that it worked.

Just for folks who might come across this post in future. The suggestion that @StevenBowden mentioned would work as well. This is the one I briefly mentioned in my very comment with 'sql windowing function'.

The difference between these two solutions are (as far as I can think of😞

• The windowing function gives you unique (mostly sequential and without any gaps) id.
  • FYI: sql windowing functions are very powerful and provides a lot of advanced capabilities
• That said, you might perform slower especially while processing a lot of records.
  • I believe indexing fields used in ORDER BY clause would help
  • also the field(s) you are using in the ORDER BY clause can't produce unique identifier for each row, then you row_number() might not deterministic especially with rows with same values.
• whereas, the option that provided is brain-dead simple, but you might run into some gaps in objectid... also i don't see any issue in performance

[StevenBowden · Accepted solution]

Hi Kathryn,

I use something like this to create objectids.  This is from sqlserver 

cast(row_number() OVER (ORDER BY gis.water_valves.asset_id) AS int) AS objectid,

In relation to you seeing more features than should be there, make sure that the feature classes aren't registered as versioned etc because if they are then you would need to use the <tablename>_evw views instead of the the base table name in the SQL.  The _evw view is where it joins the adds and deletes tables with the base table all back together to give the "real/current" picture of the feature class.

Regards

Steve

[KathrynWesson]

Wow, my Google-ing never led me to _EVW but your advice worked perfectly! In case anyone else stumbles across this post, here is my working View Definition, which also incorporates @TanuHoque's suggestion:

(SELECT OBJECTID + 1000000 as OBJECTID, Length, SURV_DATE, SURV_CREW, EXEMPT_COD
FROM TR.TR_ADMIN.CRCP_EVW
UNION
SELECT OBJECTID + 2000000 as OBJECTID, Length, SURV_DATE, SURV_CREW, EXEMPT_COD
FROM TR.TR_ADMIN.Flexible_EVW
UNION
SELECT OBJECTID + 3000000 as OBJECTID, Length, SURV_DATE, SURV_CREW, EXEMPT_COD
FROM TR.TR_ADMIN.Gravel_EVW
UNION
SELECT OBJECTID + 4000000 as OBJECTID, Length, SURV_DATE, SURV_CREW, EXEMPT_COD
FROM TR.TR_ADMIN.Rigid_EVW)

Thanks for your help!