After working with ArcIMS for 7 years, I've learned to accept some limitations. Sometimes though, requirements dictate that we "bend the rules" a bit. One recent challenge was to determine the distinct values for a field in an ArcIMS layer. Since ArcIMS queries have no "distinct" clause, we had to get creative. Here are the two options we came up with:

  1. Query for a bunch of records (say, 50). Get the unique field values from those 50, and query again, this time filtering out records that are NOT the ones we've just found. Repeat until you get no new records from ArcIMS or until you have more records than you can handle, whichever comes first.
  2. Formulate a special WHERE expression, in the form OWNER.SCHEMA.TABLE.OBJECTID IN (SELECT MAX(OBJECTID) FROM TABLE GROUP BY UNIQUE_FIELD). A real-life example that can be used on our Geocortex IMF Demonstration Site (try the query builder on the Geocode Streets layer) is: SDE_CHAR_VMB.SDE_CHAR_VMB.CNTY_STREETS_V.OBJECTID IN (SELECT MAX(OBJECTID) FROM CNTY_STREETS_V GROUP BY SUBDIVISIO)

The first option is the only possibility for shapefile-based data sources, and for SDE data sources with non-unique OBJECTIDs. I'm partial to the second one though because it takes only one query, it's very fast (even for finding the 200 unique values in layers with hundreds of thousands of features), and it showed me that subqueries in WHERE expressions to ArcIMS are possible.

I'm curious to see what other clever things can be done with ArcIMS using sub-queries...