Hi,
I am developing an application using ArcGis Runtime v100.2 and ArcGis server v10.4.
I have a layer with multiple objects with the same shape and I'm using layer definition queries to select objects which are shown to the user.
To keep things simple my objects are stored in the database in table Objects with columns itemId, value, timestamp and shape.
Items with the same itemId have always the same shape and for one timestamp one itemId can have at most one value.
The approach works if the definition query is very simple (e.g. timestamp = '2018-01-01 12:00') but it doesn't work when I use something more complicated like (selecting most recent value for given point in time):
timestamp = (select max(timestamp) from Objects t2 where timestamp <= '2018-01-16 17:00' and itemId = t2.itemId )
In such case always all objects are shown. Moreover I cannot find any error or warning message anywhere.
(Documentation suggests using IN instead of first = but the query doesn't work both cases.)
The same behaviour I see when I use the web REST interface.
I have tried profiling the database, and it seems that the query isn't run in the database at all.
I have also tried running the same query in the ArcMap 10.4 and it works fine there (and I can see the query in DB profiler) so I believe that the query must be correct. It of course works also if run directly in the DB.
My questions are:
1. What do I do wrong?
2. Are the errors in layer definition queries logged anywhere?
3. Is there any detailed information about layer definitions query syntax?
For quick tests the query that also doesn't work in the REST interface but works in the ArcMap is:
itemId % 2 = 3
Where itemId can be any integer column, % is the MOD operation. In the REST interface it returns all items (and it's not run against the database) and in the ArcMap it rerurns no results (which is of course correct because there are no integers such that x % 2 = 3).
Best regards,
Mateusz
