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
