topic REST Query at 9.3.1 with joined data in ArcGIS REST APIs and Services Questions https://community.esri.com/t5/arcgis-rest-apis-and-services-questions/rest-query-at-9-3-1-with-joined-data/m-p/518527#M2468 <HTML><HEAD></HEAD><BODY><SPAN>I have map service in 9.3.1 sp2 which contains a feature class (parcels) joined on "pid" to a table (pdata).&nbsp; All data is contained in the same file gdb and relevant fields are indexed.&nbsp; I'm using 2 different Query operations with different problems ...</SPAN><BR /><BR /><SPAN>The first query is attribute only with a where clause like this:</SPAN><BR /><SPAN>parcels.pid in (select pdata.pid from pdata where upper(pdata.owner) like upper('%[value]%'))</SPAN><BR /><BR /><SPAN>The above clause works if I choose the default join option of "Keep all records" but it takes 15-20 seconds.&nbsp; However, when using "Keep only matching records", the query fails with an "invalid SQL statement was used" error.&nbsp; If I remove the first "upper" function, the query works and returns the correct results almost instantly.</SPAN><BR /><SPAN>parcels.pid in (select pdata.pid from pdata where pdata.owner like upper('%[value]%'))</SPAN><BR /><BR /><SPAN>Can I not use the upper function on a table field in a subquery?</SPAN><BR /><BR /><BR /><SPAN>The second query is a spatial query with a simple envelope for the query geometry.&nbsp; The query should return 19 parcels.&nbsp; If I use the join option of "keep all records", the operation works correctly.&nbsp; However, when "keep only matching" is used, there are some problems...</SPAN><BR /><BR /><SPAN>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.&nbsp; All of the returned geometries appear to have the correct coordinates and spatial reference.&nbsp; So, that's strange ...</SPAN><BR /><BR /><SPAN>The really strange part is that I use the REST Query page directly, with HTML or JSON output, I get the 1st 500 parcels.&nbsp; BUT, if I change the output to KMZ, I get the correct 19 parcels.&nbsp; Nothing else about the query changes except the output format!</SPAN><BR /><BR /><SPAN>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.</SPAN><BR /><BR /><SPAN>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?</SPAN></BODY></HTML> Thu, 30 Sep 2010 15:29:42 GMT DavidGreene 2010-09-30T15:29:42Z REST Query at 9.3.1 with joined data https://community.esri.com/t5/arcgis-rest-apis-and-services-questions/rest-query-at-9-3-1-with-joined-data/m-p/518527#M2468 <HTML><HEAD></HEAD><BODY><SPAN>I have map service in 9.3.1 sp2 which contains a feature class (parcels) joined on "pid" to a table (pdata).&nbsp; All data is contained in the same file gdb and relevant fields are indexed.&nbsp; I'm using 2 different Query operations with different problems ...</SPAN><BR /><BR /><SPAN>The first query is attribute only with a where clause like this:</SPAN><BR /><SPAN>parcels.pid in (select pdata.pid from pdata where upper(pdata.owner) like upper('%[value]%'))</SPAN><BR /><BR /><SPAN>The above clause works if I choose the default join option of "Keep all records" but it takes 15-20 seconds.&nbsp; However, when using "Keep only matching records", the query fails with an "invalid SQL statement was used" error.&nbsp; If I remove the first "upper" function, the query works and returns the correct results almost instantly.</SPAN><BR /><SPAN>parcels.pid in (select pdata.pid from pdata where pdata.owner like upper('%[value]%'))</SPAN><BR /><BR /><SPAN>Can I not use the upper function on a table field in a subquery?</SPAN><BR /><BR /><BR /><SPAN>The second query is a spatial query with a simple envelope for the query geometry.&nbsp; The query should return 19 parcels.&nbsp; If I use the join option of "keep all records", the operation works correctly.&nbsp; However, when "keep only matching" is used, there are some problems...</SPAN><BR /><BR /><SPAN>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.&nbsp; All of the returned geometries appear to have the correct coordinates and spatial reference.&nbsp; So, that's strange ...</SPAN><BR /><BR /><SPAN>The really strange part is that I use the REST Query page directly, with HTML or JSON output, I get the 1st 500 parcels.&nbsp; BUT, if I change the output to KMZ, I get the correct 19 parcels.&nbsp; Nothing else about the query changes except the output format!</SPAN><BR /><BR /><SPAN>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.</SPAN><BR /><BR /><SPAN>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?</SPAN></BODY></HTML> Thu, 30 Sep 2010 15:29:42 GMT https://community.esri.com/t5/arcgis-rest-apis-and-services-questions/rest-query-at-9-3-1-with-joined-data/m-p/518527#M2468 DavidGreene 2010-09-30T15:29:42Z