Query Layer and Attribute Table contain different values

[MatthewSmart451]

Hello, I am trying to work out why when I query a table why I get a different result than what is contained in the attribute table? Field I'm interested in is RCODE_NO, ID is unique, and the SQL query is a simple one:

SELECT PIN, ID, RCODE_NO FROM ArcSDE.GISDBA.City_TPS24New WHERE PIN=123998

What I find extremely strange about this is I copied the table into a new Enterprise Geodatabase in Catalog and it seems to work as expected?

Any help would be greatly helpful, SQL novice here 😅

[Bud]

1. Can you remove the file geodatabase label from this post, since it’s not a file geodatabase? File geodatabases don’t support query layers.
2. What version of SQL Server?
3. What version of ArcGIS Enterprise?
4. What version of Pro?
5. Are you sure the field you’ve chosen as the unique ID field is truly unique and doesn’t have nulls?
6. Can you clarify what the problem is? Is the RCODE_NO value wrong in the query layer?
7. Can you show us a screenshot of the Fields View in ArcGIS Pro for both the underlying database table and the query layer?

[MatthewSmart451]

1. Done
2. Microsoft SQL Server 2017 14.0.3451.2 (Build 14393: )
3. Enterprise 10.8.1
4. ArcPro 3.2.2
5. Upon closer inspection, no the field is not unique and yes there are nulls.
6. The RCODE_NO value in the query layer should be "R60"
7. See below (cannot get query layer field views as the option is greyed out):

Is the data versioned?
Yes, traditionally versioned.

[Bud]

My experience is that most query layer problems stem from the unique ID field. Hopefully, if you clean up that field, your issues will go away.

[MatthewSmart451]

Will give that a crack, cheers!

[George_Thompson]

I would also ask if the data is versioned?

[MatthewSmart451]

Yes, the feature datasets are both versioned (traditional).