Select by Attribute SQL limitations (query used to work)

Matt-Goodman

Hi, I'm using ArcGIS Pro 3.3.5 and trying to query a feature class in an Enterprise SQL db to select records with duplicate ID's.

The query below used to work for me, regularly. I used to run it at the end of each day without an issue, but it recently stopped working:

[ADD_ID] IN
(SELECT [ADD_ID] FROM [sde].[stlouis].[ADDRPLCS_MARADDRESS]
GROUP BY [ADD_ID] HAVING COUNT(*) > 1)

I suspect it was never supported or supposed to work and probably a recent SQL server upgrade ended it. My sense is that this is a known limitation, an unsupported operation, based on the Esri documentation prescribing methods to find duplicates (https://support.esri.com/en-us/knowledge-base/how-to-identify-duplicate-or-unique-values-in-arcgis-p...). The problem is that all of the supported methods seem to require population of a new attribute field, or outputting a new feature class. Isn't there a way to just select them? This seems like such a foundational SQL function, it's surprising that it isn't supported through the Pro interface.

Does anyone know more precisely why my SQL query above would have stopped working, or any other work-around methods for making this selection work? A SQL select via the python window, perhaps?

Thanks,
-Matt

JoshuaBixby

The SQL statement itself is pretty basic, but SQL support does vary across different database platforms, so it is always good to clearly state the database and version. You mention "SQL server upgrade" but some people generically call databases "SQL server" so I tend not to assume they mean MS SQL Server.

Also, what exactly do you mean by "stopped working?" Does it return an error? If so, what? Does it give unexpected results? If so, what are you expecting and what are you getting? Are you sure there are any duplicates at the moment?

Matt-Goodman

Good clarifying questions, @JoshuaBixby ...

The enterprise geodatabase I'm querying is Microsoft SQL, a ArcGIS Pro 3.3.2 Enterprise geodatabase (upgraded from 10.7.1 in February).
When I run this SQL query in ArcGIS Pro, I get the spinning cursor (processing) and it never resolves. I have to use Windows Task Manager to "End task" and restart the software.

The query does run as expected when it's executed in SSMS on the exact same data table (query syntax has to be slightly modified for that use).

JoshuaBixby

If this is versioned data, then the exact same query run in SMSS isn't actually the same. If the data is versioned, then within Pro the SQL is actually hitting the versioned data whereas the same SQL in SMSS will only be hitting the base tables. If versioned, try creating a new version from default and see if the query works.

Matt-Goodman

The feature class is not versioned. The project is pointing at the default.

JoshuaBixby

You say when run in Pro you get the spinning cursor and it "never resolves." How long have you actually waited? If only a minute or two, have you tried letting it run for 15 or 30 minutes just to see if it generates results? There is a difference between something not working, i.e., broken and can't work, and something performing very very slowly. If the data set is large and something is messed up with its indexes or the database has other resource issues, it may be the query will work but take a disproportionate amount of time.

Matt-Goodman

As I dig in deeper, I'm starting to think I have a uniquely glitchy bug.

Starting with a totally clean project/map, with only one single feature class I can get the query to work normally, especially when I intentionally put duplicate ID attributes for it to select. I can run the Select By Attributes and it will select them. However, when I run or validate the query while there are not any duplicates present for it to find, it behaves abnormally:

When validating (clicking the green check mark) it returns a message that the query is valid, but it does not also give the usual message stating that no records were returned.
When executing the query, it appears to do nothing and then when closing out of the Select By Attributes dialog, the cursor turns to the "processing" wheel indefinitely (or at least for more than 30 minutes, which is debilitating).

So far the only field that seems to cause this issue is the ID field that I'm trying to check, which is a 36 character UUID (but not an Esri GlobalID and not managed by the database). I can perform the same test on, for example, the address number field, and it successfully selects the many duplicate address numbers.