Query with "nvl" not working at 10.2, works at 10sp5

[JeffPace]

I have a query (where clause) that does not work at 10.2
The identical query works at 10sp5.  Data are stored in oracle

any ideas?

nvl(PROP_HN,' ') LIKE '1112%'
AND nvl(PROP_SN,' ') LIKE UPPER('MANATEE%')
AND nvl(PROP_ST1,' ') LIKE UPPER('AVE%')
AND nvl(PROP_ST2,' ') LIKE '%'
AND nvl(PROP_DIR,' ') LIKE '%'
AND (nvl(PROP_PLC,' ') LIKE '%'
OR nvl(PROP_PLC,' ') LIKE '%'

[JeffPace]

forgot to add, changing the query to objectid =1 works, so i am confident everything else is working

[JeffFerdinand]

I am having the same problem.  The problem looks like it is specifically related to NVL.

Here is my where clause:
nvl(E_ROUTE,'blank') = 'blank' or PROC_FLAG = 'NEW'

This works PROC_FLAG = 'NEW'

Also, decode(), replace, and several others do not work. 

I installed ArcGIS Server 10.2.1.  I also updated my Oracle drivers on the server.  The problem still persists.

[JeffPace]

This is really not good if this is the case, and would cause a rewrite on all our queries.

[JeffFerdinand]

Also,

I thought there might be a chance that the query was failing at the database level.  Perhaps some bad data was causing NVL to return an error.  This is not the case. Essentially the same query works at the database level.

This is being logged by ArcGIS Server
<Msg time='2014-02-06T06:49:55,58' type='SEVERE' code='10849' target='ETS/Meter_Routing.MapServer' methodName='MapServer.Query' machine='GIS1.NTLAN.LVVWD.COM' process='9280' thread='6176' user='anonymous' >An invalid where clause or definition expression has been requested: "nvl(E_ROUTE,'blank') = 'blank' or PROC_FLAG = 'NEW'"</Msg>

[MichaelVolz]

Have you tried this query on multiple computers at either 10.2 or 10.2.1?  If so and it fails on all computers then I would log an incident with ESRI.

What is your datasource?

[JeffFerdinand]

In my particular case, we are querying an SDE feature class, unversioned, residing in Oracle 11g (11.2.0.3.0). ArcGIS Desktop is able to execute this.  I exectuted this 2 ways: by "Select By Attributes", and as a query filter on the ArcMap Layer.

The only noticeable difference in my case is ArcGIS Desktop is using a 32bit version of Oracle Client while ArcGIS Server is user a 64bit version of Oracle Client. 

I am going to log this with ESRI. It appears to be a parsing problem specific to ArcGIS Server 10.2 and 10.2.1.  As mentioned by another member, this did work at ArcGIS Server 10.0 sp5.  It also worked in ArcGIS Server 10.1.

There are ways around this. I do appreciate everyone's quick responses.

[MichaelVolz]

Jeff F:

Did you try installing the 32 bit version of Oracle Client on the ArcGIS Server?  I ask because even though my organization has an ArcGIS Server server that is 64 bit, we still used a 32 bit Oracle Client (Its been working for years).  The 32 bit Oracle Client has been around longer, so it might be that there are still some bugs to work out with the 64 bit environment.

[JeffFerdinand]

I did wonder about that.  I think I will try that.

[RussCoffey]

In addition to the NVL, Decode and other functions that Jeff F. mentions, subselects are also not returning.  At first, when we hit the NVL problem, I just started coding around it.  However, with more than a dozen Silverlight applications hitting various REST endpoints and the fact that it seems that anything more complex than a simple select statment will not work, our users are feeling the impact of non-performing applications.
We are going to test the 32-bit Oracle client suggestion, and will report our results here.