AnsweredAssumed Answered

Issue with map service REST query "sum stat" with Oracle/archiving enabled

Question asked by simpsonr on Sep 6, 2016

I'm having an issue with an Oracle SDE based map service feature layer query operation returning back incorrect "sum" statistic values.  The issue seems to be related to archiving being enabled on the feature class and the map service query returning a sum for all the records in the feature class, which if archiving is enabled, is far more records than actual features in the feature class.   If I turn off archiving, the sum operation works correctly, that is, if I have 5  points in the features class with a number field called "testnumfield" and each one has that field value populated at the number 10.... the sum would be 50.  If I go in and edit that feature class and change one of the values to 20...  The sum operation should return 60 but in fact returns 70, which is the original 50 + the new 20.  Since archiving retains a history of the values of fields, it seems that this REST endpoint "sum" operation is erroneously taking into account all those values and not just the values of the current state of the feature class.  See below.  For simplicity, I calculated all the values of "testnumfield" all back to 0, but they have been calculated to various values over the past few days.  The first link shows that no features have a "testnumfield > 0".  However, the second link, on the same map service/feature layer, performs a "sum" on that field.  The result should be 0, since no features have a value greater than 0 for that field.  But the result is a number much bigger than zero.  The number actually is reflecting all the values that have been given to those points over the past few days, even though they are all currently calculated to 0.  I've verified this by opening the raw Oracle tables in PL SQL Developer and examining the attribute table.  All the archived records and associated "testnumfield" values are there.

 

Interestingly, I've tried this same test via hosted map service in AGOL with no issues.  Either ArcGIS Server/AGOL is doing something differently or this is a database specific issue and AGOL and esri ArcGIS Server uses SQL Server or some other backend DB.

 

First link:  Select all features with a "testnumfield" value greater than zero.

Query: GIS.testPoints (ID: 0) 

 

Second link:  Sum all the values of "testnumfield" for the same point feature class.  The result should be 0.  The result of "100" reflects archived values for previous edits to the "testnumfield" for those features.

Query: GIS.testPoints (ID: 0) 

 

I currently have a ticket in with esri on this issue but it's been stuck at "we can't duplicate this issue".  Since they are not testing this on their end using Oracle, that is not a surprising answer.  

 

This is a critical function for me to be able to perform as our whole incident management mapping website depends on Operations Dashboard and key "sum" values for structure damages.  The "gauge" widget uses the exact sum operation presented above in the first link.

 

Any help would be greatly appreciated.

Much Thanks,

-Royce

Outcomes