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?

• travis: An Oracle database isn't considered file based, so it does support subqueries, You have a function not a subquery. Can you select the result of gis_getmeterstatus (try: select gis_getmeterstatus(METERNAME,'2013年08月08日 10:25:00','2013年08月09日 10:25:00') from gis_meters where rownum < 10)? A subquery would be something like: Select * from gis_meters Where 0 not in (Select status from gis_meterhistory where logTime > :lowDate and logtime < :highDate)

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.