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.
I think this will work... my field is called TxtTest though
This was written is SQL Server but should work the same:
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:
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
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!
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