ArcGIS - Help Using Oracle Function in Definition Query - No ArcSDE

Question

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?

Answer 1

I don't have any examples anymore, but I'm pretty sure I have been able in the past to do functions in subqueries within the definition expression. The trick was to select objectids. Something like:

objectid in (select objectid from gis_meters where gis_getmeterstatus(METERNAME,'2013/08/08 10:25:00','2013/08/09 10:25:00') = 0)

Better yet, why aren't you putting the definition query language directly into the query layer? It can be modified in source dialog.