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-pr-000023355). 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

Matt-Goodman

Still don't have any idea what's going on. 

I tried a fresh/clean .aprx with just one single SDE data set in it. I ran this Select By Attributes looking at an integer field, [mar_id] and the application was basically incapacitated for over an hour (I quashed it to go home for the day).

It did, after about 40 minutes, return the message stating "The SQL expression is valid, but no records were returned." But even after that message, I was stuck with limited functionality as the 'processing' cursor continued to spin. Even after closing the Select By Attributes dialog, the cursor remained and context menus were all grayed-out.