AGOL can't access table data in SQL View with PIVOT

228
1
12-09-2020 10:07 AM
Labels (1)
JayJohnsonWashoeCounty
Regular Contributor

I have created two SQL Views (non-spatial) in Catalog (v10.8), published them via Pro (2.6.3) as a service, and added them to AGOL as items via their REST endpoints, specifying to store the credentials with the items. The first View, which uses a GROUP By function works fine in AGOL.  

The second View, which uses a PIVOT function gives me a "Data Error" when trying to view Data in AGOL and a "Cannot access data" error in Dashboard.  This PIVOTED view works fine in Pro and I can query the REST endpoint successfully.

View SQL is shown below.  Thoughts on why this won't work in AGOL?

-------------------------------------------

SELECT cast((ROW_NUMBER() over (order by RACE asc)) as int) as OBJECTID, RACE, GENDER, [SUICIDE],[NATURAL CAUSES],[NARCOTICS RELATED],[EXCITED DELIRIUM],[HOMICIDE]
FROM
(SELECT GENDER, RACE, CAUSE_OF_DEATH
FROM xxxxxx.DEATHSSINCE2000) as SourceTable

PIVOT
(
COUNT(CAUSE_OF_DEATH)
FOR CAUSE_OF_DEATH IN ([SUICIDE],[NATURAL CAUSES],[NARCOTICS RELATED],[EXCITED DELIRIUM],[HOMICIDE])
) AS PivotTable

---------------------------------------------------

Jay
Washoe County GIS
Tags (4)
0 Kudos
1 Reply
JayJohnsonWashoeCounty
Regular Contributor

Adding some mentions to likely-looking ESRI staff:

@ChristianWells 

<<mention removed at user request>>

@JakeSkinner 

Jay
Washoe County GIS
0 Kudos