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?

  • 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) – travis Aug 10 '13 at 20:00

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.