topic Re: Query Layer and Attribute Table contain different values in Geodatabase Questions

Query Layer and Attribute Table contain different values

MatthewSmart451 — Fri, 24 May 2024 04:19:38 GMT

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 😅

Re: Query Layer and Attribute Table contain different values

Bud — Tue, 21 May 2024 10:03:26 GMT

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

Re: Query Layer and Attribute Table contain different values

George_Thompson — Tue, 21 May 2024 11:15:03 GMT

I would also ask if the data is versioned?

Re: Query Layer and Attribute Table contain different values

MatthewSmart451 — Fri, 24 May 2024 06:19:23 GMT

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.

Re: Query Layer and Attribute Table contain different values

MatthewSmart451 — Fri, 24 May 2024 06:20:06 GMT

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

Re: Query Layer and Attribute Table contain different values

Bud — Fri, 24 May 2024 13:01:03 GMT

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.

Re: Query Layer and Attribute Table contain different values

MatthewSmart451 — Sun, 26 May 2024 23:45:09 GMT

Will give that a crack, cheers!