I have set up a Query Layer pointing at an Oracle table with a SDO.Geometry column, and that is working and behaving fine.
However, when I am attempting to set up a definition query that calls a Oracle function, I get an error message saying "There was an error with the expression. Failed to parse the where clause".
I am fairly confident the SQL expression I am using is valid: it works fine inside of SqlDeveloper. Here is what the sql looks like:
select * from gis_meters where gis_getmeterstatus(METERNAME,'2013/08/08 10:25:00','2013/08/09 10:25:00') = 0
In the Query Builder of ArcMap, I am only adding, as the query layer alread has the select *:
gis_getmeterstatus(METERNAME,'2013/08/08 10:25:00','2013/08/09 10:25:00') = 0
After some research, I found some ESRI documentation stating "Coverages, shapefiles, and other nongeodatabase file-based data sources do not support subqueries". I am guessing that the call to the Oracle function (gis_getmeterstatus) is a subquery - and thats why its not working for me? Since I don't have SDE installed in the database, its not considered a geodatabase.
Can anyone help me find a way to use this function in my definition query?