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_AREACountMetric199Metric22Metric373 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.

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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.

Finde mehr Beiträge mit dem Tag

Geodatabase

Feature Services

Accepted answers

All comments

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.

CaseyWilson1

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

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-geodatabase.htm

AmyRoust

We had very similar experiences, @KimberlyGarbade. (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

KimberlyGarbade

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

Thank you, @KimberlyGarbade! 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:

KimberlyGarbade

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

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

Nimm an der Unterhaltung teil

Angemeldete Mitglieder können Beiträge verfassen, Updates folgen und mehr. Neu hier? Registriere ein kostenloses Konto.

Schnellzugriffe

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.