I have map service in 9.3.1 sp2 which contains a feature class (parcels) joined on "pid" to a table (pdata). All data is contained in the same file gdb and relevant fields are indexed. I'm using 2 different Query operations with different problems ...
The first query is attribute only with a where clause like this:
parcels.pid in (select pdata.pid from pdata where upper(pdata.owner) like upper('%[value]%'))
The above clause works if I choose the default join option of "Keep all records" but it takes 15-20 seconds. However, when using "Keep only matching records", the query fails with an "invalid SQL statement was used" error. If I remove the first "upper" function, the query works and returns the correct results almost instantly.
parcels.pid in (select pdata.pid from pdata where pdata.owner like upper('%[value]%'))
Can I not use the upper function on a table field in a subquery?
The second query is a spatial query with a simple envelope for the query geometry. The query should return 19 parcels. If I use the join option of "keep all records", the operation works correctly. However, when "keep only matching" is used, there are some problems...
The query will return the first 500 records of the parcels layer and place them in Africa instead of Florida .. no matter what the spatial query. All of the returned geometries appear to have the correct coordinates and spatial reference. So, that's strange ...
The really strange part is that I use the REST Query page directly, with HTML or JSON output, I get the 1st 500 parcels. BUT, if I change the output to KMZ, I get the correct 19 parcels. Nothing else about the query changes except the output format!
It appears that there is something different in the Query handler when the output format is KMZ that is correct, but is broken with other output formats when a feature class is joined to a table with the "keep only matching" join option.
Any ideas on how I can get both queries to work correctly and efficiently staying with 9.3.1 sp2 and keeping the table dynamically joined to the feature class?
