Adding a Unique ID/ OBJECTID to a SQL Query Layer that uses GroupBy

[AmyRoust]

I love using SQL views to summarize my data for use in Dashboards and Experience Builder. However, I keep running into the problem of not having a unique identifier for Pro to use. Here's an example query:

SELECT CONTENT_AREA, COUNT(CONTENT_AREA) AS Count
FROM SDE.Dataset
GROUP BY CONTENT_AREA

That gives me a nice table that looks like this:

CONTENT_AREA	Count
Metric1	99
Metric2	2
Metric3	73

This query gives me the numbers I need, but Pro can't add it to the map because there is no unique identifier. In other queries that don't use the GROUP BY clause, I can use this trick:

CAST(ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS INT) AS OBJECTID

... but this doesn't work in Pro unless I include OBJECTID in the GROUP BY clause.

[KimGarbade]

I think this will work... my field is called TxtTest though

This was written is SQL Server but should work  the same:

[AmyRoust]

Thank you, @KimGarbade! That partially worked, but didn't get me all the way across the finish line. In SSMS, the query works, but when I try to save it as a view, I get this error message:

I saved anyway and tried to add the table to Pro, but got this message when I tried to open the attribute table:

Then I tried adding the actual query as a query layer in Pro. That worked - it added to the map and I was able to open the attribute table and view its contents. But, when I tried to publish a REST service to our stand-alone ArcGIS Server, it gave me several unhelpful errors:

[KimGarbade]

Hmmm...Sorry this is going to be long.

When I tested in my home environment I saw different messages and worked around them until it worked.  I realize our environments are different, but maybe my experience will give you a clue.

I will walk you through what I did (keeping in mind we're almost certainly on different versions of everything)

I created the View in SSMS just in a query window and it worked for me:

Adding this view to my map in Pro did not work initially, returning this error:

To fix this I opened the Properties > Source for the view in ArcGIS Pro and edited the SQL Query it was using to simply remove the double quotes it had put around "COUNT" for some reason

Once I did that the view table would open in Pro, but oddly it added an ESRI_OID field which in effect is what you wanted all along, and makes the "UniqueID" field I calculated in the view obsolete:

So finally I Altered my view by removed the "Row_Number() Over(ORDER BY TxtTest asc) as UniqueID," part ,re-added it to my map in Pro, and edited the query to remove the double quotes around "COUNT" and got this when viewing the view table in Pro 

[AmyRoust]

We had very similar experiences, @KimGarbade. (I laughed when you made the comment about the mysterious ESRI_ID field appearing. What the heck??) The only thing I can't replicate in your second answer is in the last step. I cannot find a way to add the modified view to Pro if it doesn't have the UniqueID field. 

Fortunately, between your tips and the suggestion from @Reinaldo_Cartagena, I thought up a new solution. I saved my view without a unique id, and then created a second view where I joined my first view to a master table that contains a single list of all CONTENT_AREA values. In that second query, I added the OID from the master table and got what I needed.

I have a hunch that Reinaldo's solution is more elegant than what I did, but I couldn't quite parse out his example in my own mind. (I've never been good at nested queries.)

Appreciate the help!

[Reinaldo_Cartagena]

Hi, one option to include other columns is to replace the GROUP BY clause with an SQL expression with a Sub Query or nested query. Annex I send an example adapted to your case.

SELECT DISTINCT OBJECTID, CONTENT_AREA, (SELECT COUNT(CONTENT_AREA)
FROM SDE.Dataset AS SDE_Dataset_B
WHERE SDE_Dataset_B = SDE_Dataset_A) AS Count
FROM SDE.Dataset AS SDE_Dataset_A

[DonSjoboen]

Your ROW_NUMBER function is missing one key thing... that is the ISNULL () function needs to be added in front of ROW_NUMBER.

SELECT ISNULL(CAST(ROW_NUMBER() OVER

  (

    ORDER BY [field]

  ) AS int), replacement_value --this value can be 0, since it is highly unlikely that your ID will return a Not NULL value since you are using the ROW_NUMBER function--) AS [ID]

Correction to my previous comment about the ISNULL replacement value: the replacement_value for the ISNULL function returns the given value if the expression returns a NULL value.

ISNULL (Transact-SQL) - SQL Server | Microsoft Learn

You should NOT include OBJECTID from the parent table in your views (see article below).  That is why you are seeing the ESRI_OID in Pro when using "Query Layer".  Esri needs a unique integer value that is not null.

https://pro.arcgis.com/en/pro-app/latest/help/data/geodatabases/manage-sql-server/views-in-geodataba...

[CaseyWilson1]

Thank you DonSjoboen. The addition of isNull did the trick for me to create a recognized ObjectID value in my Database View. 

[DonSjoboen]

Glad it worked out for you.  I amended my previous reply and added the correct description/reason for the ISNULL replacement_value part of the function, along with SQL Server documentation.