SQL View not working correctly as a service

DarrenHaag — Sun, 12 Dec 2021 09:40:05 GMT

I have multiple query layers and SQL spatial views in our environment which we serve out to our Enterprise Server. All of them work fine except for one. In it, I am specifying a derived field as Days Since Issued. I use this same function in other views and it works correctly. However, in this one particular service, it seems it is treating the DATEDIFF(DAY, issued,GETDATE()) as a static field. Meaning, the day I serve out the service, that day is saved and the field isn't updated beyond that day. (so what was 1 day from being issued on the day I served it out to our enterprise server remains 1 day no matter how long ago it was). The views are registered with the Enterprise database (10.6.1) and appear correctly in ArcMap and in ArcPro.

I'll provide example code

/*First Query that doesn't work as expected when a service*/
SELECT cast(ROW_NUMBER() OVER (ORDER BY a.case_number) AS int) AS OBJECTID
     ,a.case_number, a.case_name, a.location, c.case_status, b.case_type
     ,b.case_type_desc, a.date_entered, a.date_issued, a.date_expiration 
     ,iif(a.date_issued IS NULL, NULL, DATEDIFF(DAY, a.date_issued, GETDATE())) AS daysSinceIssued
     ,geometry::Point(a.cx, a.cy, 3419) AS shape
FROM ...Table1 a LEFT JOIN
     ...Table2 b ON a.case_type_id = b.case_type_id LEFT JOIN
     ...Table3 c ON a.case_status_id = c.case_status_id
WHERE b.case_type LIKE 'BLDC%' AND a.cx > 1800000



/*Second Query that does work as expected when a service*/
SELECT CAST(ROW_NUMBER() OVER (ORDER BY requestid ASC) AS int) AS OBJECTID
     ,requestid, problemcode, description, details, priority, probaddress
     ,reqcategory, submitto, datetimeinit, datetimeclosed, PRJCOMPLETEDATE, status
     ,iif(status IN ('CLOSED', 'COMPLETE'), CASE WHEN isnull(datetimeclosed, 
          GETDATE()) < isnull(prjcompletedate, GETDATE()) THEN datetimeclosed ELSE prjcompletedate END, NULL) AS dateclosed
     ,DATEDIFF(DAY, datetimeinit, GETDATE()) AS daysSinceRequest
     ,iif(datetimeclosed IS NULL, NULL, datediff(day, datetimeinit, datetimeclosed)) AS daysToClose
     ,geometry::Point([srx], [sry], 3419) AS shape
FROM ...Table1
WHERE [initiatedby] = '#############' AND [srx] > 1800000‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Excerpt from above where I'm seeing a difference

/*This acts as a static field which is not how I would expect*/
iif(a.date_issued IS NULL, NULL, DATEDIFF(DAY, a.date_issued, GETDATE())) AS daysSinceIssued

/*This acts how I would expect as a dynamic field and keeps the values up to date*/
DATEDIFF(DAY, datetimeinit, GETDATE()) AS daysSinceRequest‍‍‍‍‍‍‍‍‍‍

results as a service:

This query correctly updates the daysSinceRequest field (today's date 9/24/2018)

This query does not correctly update the daysSinceIssued field (today's date 9/24/2018)

Any thoughts or ideas are appreciated. I've pushed out a fresh copy of the service that doesn't work to see if it will update correctly by chance there is just an underlying issue with the current service that isn't being fixed when overwriting it.

My only other thought is is could be with what I'm creating the objectid on. I know requestid's are created with the next available sequential number. While case_number is unique, I can't guarantee they are sequential when added. Maybe the rearranging of objectid's is causing the issue if that is indeed occurring. Yet there hasn't been a new issued case since this has been served out and still isn't updating.

topic SQL View not working correctly as a service in Geodatabase Questions

SQL View not working correctly as a service