Problem: Keyword UPPER not working with SpatialQuery
*This issue has been resolved in ArcIMS 9.0.1*
The keyword UPPER is used in SPATIALQUERY to convert lowercase or mixed case field values into upper case characters. In ArcIMS 9.0, UPPER does not work for the following case:
When UPPER is used on the right side of a WHERE clause to convert mixed or lower case field values to upper case, AND the size of field value is less than the maximum allowed length of the field.
<SPATIALQUERY subfields="#ALL#" where="UPPER(WHOLEST) = UPPER('N Tryon St')" >
ArcIMS clients that use a similar query will fail to return correct results when used with an ArcIMS 9.0 Spatial Server.
The last character of field value is truncated when using UPPER. In the example shown above, the query will search for a field value that matches the string "N TRYON S".
Solution or Workaround
There are two options available to workaround this issue:
- Option 1:
Use a trailing space or any other character after the field value when the size of field value is less than the maximum allowed length of the field. In the example below, an asterix (*) is included at the end of field value used in query.
<SPATIALQUERY subfields="#ALL#" where="UPPER(WHOLEST) = UPPER('N Tryon St*')" >
- Option 2:
Pre-process field values on the client side and convert all characters to uppercase before using it in the query.