Query Layer Feature Count Does not Match SQL Server Management Studio

5845
8
Jump to solution
03-26-2016 02:52 PM
MeleKoneya
Occasional Contributor III

I created a query in SQL Server Management studio using a SQL Server ArcSDE multi-version view joined to another SQL table.   I am joining a signs SQL table to our sign posts feature class.   We have more signs than signposts so I am using a Left Join to get the SQL Geometry of the Sign Post feature class and assigning it to the joined sign.

When I run the query SQL in management studio,  I see about 45,000 records returned.

When I take that same SQL statement and create a Query Layer from it,  the number or records in the Query Layer's attribute table is about 29,000.

If I than export the Query Layer to a shapefile,  the number of records exported matches exactly the roughly 45,000 I expected to see.

I have seen these types of discrepancies in counts before with SQL Query Layers when used in ArcMap.    What is the cause of this issue?   It makes it hard to trust what is shown in ArcMap when view the Query Layer directly.

Thanks,

Mele

0 Kudos
1 Solution

Accepted Solutions
JoshuaBixby
MVP Esteemed Contributor

I assume AssetID is unique in TRF_Signposts_VW and not in SignsGISMapping, right?  Since you are doing a left join back onto SignsGISMapping, the AssetID isn't really unique anymore, but you are telling the software to make it unique.  It does this by selecting the first record it finds with an AssetID.  If you make a multi-field unique identifier, possibly AssetID and AssetReferenceID, you should get more or all of the records.

View solution in original post

8 Replies
JoshuaBixby
MVP Esteemed Contributor

Can you provide the queries themselves, or at least an abridged query that demonstrates the issue with your data?  Also, when setting up your query layer, what did you select for the Unique Identifier?

0 Kudos
MeleKoneya
Occasional Contributor III

Joshua,

This is query I am using.

SELECT S.[AssetReferenceId]
      ,S.[AssetTypeId]
      ,S.[Name]
      ,S.[Description]
      ,S.[AssetId]
      ,S.[IntersectionNumber]
      ,S.[SignTypeId]
      ,S.[SignOrientationId]
   ,P.Shape
  FROM [STREETOPSWORKORDERDBSVR].[StreetOps].[gis].[SignsGISMapping] S LEFT JOIN StreetOps.sde.TRF_Signposts_VW P
  ON S.AssetID = P.asset_id

I use the AssetID field as the Unique Identifier.   The AssetId field is a int data type.

I also have some Query Layers where I use the ObjectID of the feature class as a unique identifier and I see the same behavior with the counts between SQL Server Management Studio and ArcMap.

I did see a similar thread that involved Oracle.   Maybe it is something for Support to address.

Thanks,

Mele

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

I assume AssetID is unique in TRF_Signposts_VW and not in SignsGISMapping, right?  Since you are doing a left join back onto SignsGISMapping, the AssetID isn't really unique anymore, but you are telling the software to make it unique.  It does this by selecting the first record it finds with an AssetID.  If you make a multi-field unique identifier, possibly AssetID and AssetReferenceID, you should get more or all of the records.

MeleKoneya
Occasional Contributor III

Joshua,

Thanks for the explanation and suggestion.   That makes perfect sense.   You are correct that the assetid on the posts is unique but once joined to the signs table it is not longer unique.    I added the Query Layer again to ArcMap and this time let ArcMap figure out the Unique Identifier(s) to use and now I am seeing the number of records I had expected.

Thanks,

Mele

0 Kudos
ANRGIS
by
Occasional Contributor

We have noticed this as well.

Making minor changes in our SQL table like updating a field with a new join value causes an issue with the query layer not updating with the updated values in the SQL view.

For instance, we have a SQL view with a unique field that joins some lookup tables. I added a new join with new lookup values and perform a definition query. I select distinct uniqueID's in the SQL table and I get 957 records. If I open up the GIS table I see 717.

IF.... I go into the GIS query layer source and simply change the query to SELECT * FROM

I then see the correct number of records.

It's almost like the layer is not updating with changes to counts in the SQL table. Even if the uniqueID is the same and is truly unique/distinct. If we're saving layerfiles of these query layers, there is an inherent risk that updates to the SQL View from which the query layer draws from, will not happen unless the user changes the Query back to SELECT *.

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

ANR GIS​, what you describe is expected behavior, both with ArcGIS for Desktop as well as other DBMS clients I have worked with.  For example, if you open SQL Server Management Studio (SSMS), connect to a database, and create a new view using SELECT * FROM ... syntax; the view will get created, but the asterisk will be replaced with an enumeration of the actual fields.  That is, the final SQL for the view doesn't actually include the wildcard.

There are far more minuses than pluses when it comes to storing a SELECT wildcard in a database view or ArcGIS query layer.  Instead of enumerating them here, I encourage you to read the following StackExchange thread:  Why is “Select * from table” considered bad practice.

0 Kudos
ANRGIS
by
Occasional Contributor

Thanks Bixby,

I looked over some of that discussion. I question though, whether those minuses directly apply to query layers in GIS. I guess it's hard to define the 'end user' in GIS, but I personally see them as those looking at the GIS data. As a GIS/Database person, I manipulate the tables/views to be what I expect the end user to see in the map. If I'm doing this through SQL, where I'm performing queries, and naming fields, I expect that to carry over to what the end user is seeing. My point is that is not the case. I don't expect my end user to be re-writing their query for their query layer every time a change is made on a table/view in the DBMS so that they can see the 'CORRECT' data. The way I would expect this to happen seamlessly, is if ArcMAP allows query layers to maintain the wildcard as the data's source.

As it sits now, it doesn't make sense to create queries/views in SQL for ArcMAP if it isn't going to realize those changes for the end user.

Granted, I'm a pretty novice DB person. I don't have much experience in administrating large, federated, databases... But I have enough experience to know when something isn't making sense to our end users. When I alter a query in our database view and the end user isn't seeing the same thing I am in SSMS, there is a problem.

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

If you are using query layers to read database views, or create de-facto database views, have you tried using Create Database View to create a database view based on SQL that plays nicely within an enterprise geodatabase?  I believe, although I haven't verified, that creating new views with the same name or updating existing views created that way will have the new schemas reflected to users when the data is loaded.

Regarding query layers apparently storing fields explicitly instead of storing wildcards, I won't try to speak for Esri, although I would be interested in some of their staff chiming in here.  I have worked with databases long enough to understand the minuses and even witness the fallout from some of them in production situations.  If you believe strongly enough in having query layers store SELECT wildcards, I encourage you to submit an idea at ArcGIS Ideas and open an enhancement request with Esri Support.  At a minimum, you should be able to eventually get some explanation from Esri Development, although you may need to escalate the request a time or two to get their attention.  And who knows, maybe they will agree with you.