SQL Query Layer View Records Display Different in Attribute Table than in Map View

[Anonymous User]

Here's the issue:

We have database tables in Microsoft SQL that have spatial fields (northing/easting), and we have a query that pulls fields from multiple tables to create a view. The view can be pulled into ArcMap easily and x,y coordinates can be displayed. The view isn't static; it is constantly being updated.

Unfortunately, it seems as though the map view and the table view is displaying separately from each other. For example, if OID 1 is highlighted in the table, a point is highlighted in the map, but it is actually a different OID (when you use the Identify tool on the highlighted point). Plus, if you zoom around the map, different points will highlight, even though the record highlighted in the table stays the same. There are other issues, too: table sorting doesn't work; unique IDs appear in the table but disappear after sorting; definition queries don't apply in the table (but seem to in the map view). 

We have an idea of where this issue is occurring, but not sure--starting with SQL or with Arc. The views we are having issues with are of course complicated. There can be multiple records for one point; for example, point 1 can have multiple records of a, b, c, and d. Most of the data are the same across a-d, but with a couple fields' data different. Records a-d may be created at different times; for example: a can be created today, while b may be created several months from now, etc. What is needed is a unique ID, so our SQL guy created a new field that assigned a value sequentially.

It seems as though the problem can be seen on the SQL side, too, so we're thinking the issue might start there, but...

I'm a Microsoft SQL novice and know just enough to be dangerous. Was wondering if anyone had an idea of what might be happening/how to fix? I know that I haven't given enough information, so please let me know what more is needed to help you answer--please be specific so I can gather the correct info! Also...I wasn't really sure where to place this question, so if you have any suggestions on getting to the correct/better audience, I'm all ears!

[ChristianWells · Accepted solution]

I think you are heading down the right path regarding the necessity of a unique identifier. I've seen similar behavior when there are duplicate values in the field that was selected as the unique identifier. Here are the rules for a unique identifier field in views, both single and composite fields can be used: Unique identifier fields in database tables—Help | ArcGIS Desktop  

There are a few ways to address the unique identifier issue:

1. Call the newID() function in the view and ensure the data type matches ArcGIS requirements
   1. This may require a CAST() function
2. Create a new field in the table (not the view) that is unique using Add Incrementing ID Field—Help | ArcGIS Desktop 

These can be challenging to track down and Technical Support may be the best to assist. Please let me know if you would like me to have a support analyst reach out to you.

[Anonymous User]

Thanks for the information. It's definitely helpful. 

Just to clarify: we have already created a unique ID field from multiple fields. However, I think it's only been "created" in the view and not in the actual database. And, due to the age of some of the data, there are null values (which was mentioned in the article you referenced). Sounds like creating a completely new field in the actual database might do the trick.

We'll definitely get in touch with Esri if for some reason the solutions proposed won't work. Thank you!