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

JayJohnsonWashoeCounty — Wed, 09 Dec 2020 18:07:08 GMT

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

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

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

JayJohnsonWashoeCounty — Thu, 10 Dec 2020 20:46:43 GMT

Adding some mentions to likely-looking ESRI staff:

@ChristianWells

<<mention removed at user request>>

@JakeSkinner

topic AGOL can't access table data in SQL View with PIVOT in ArcGIS Online Questions

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

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