I have an ArcSDE feature class that contains a date field called "SALE_DATE". This sale date represents the bid date for ALberta crown mineral rights sales. A sale occurs about twice per month (one date value approx every two weeks). Eg. for January there would be values of 1/14/2015 and 1/28/2015. There are only ever 4 sales dates release at a time (4 date values in the feature class) and they revolve as the current sale occurrs, another one is released. I need to create 4 layers from this one feature class, each representing the next sale date. The trick is I want to set them up with SQL def query syntax so that the dates automatically revolve and I dont have to manually adjust the sale date, it should just grab the next available date value that is available.

This is an example of the syntax I have for the first upcoming sale date:

  • Date functions are not standardized across SQL engines, so you really need to edit your question to specify that you're using Oracle (and which version). The version of ArcGIS would be nice as well. This is probably across the line for a Database Administrators transfer.
    – Vince
    Commented Jun 23, 2015 at 23:50
  • Because definition queries are unique to ArcGIS and Oracle database administrators may be unfamiliar with the application specifics of this technique, their usefulness may be somewhat limited. They sure can help with the basics of database queries, but you will need to get the rest of it correctly implemented! Commented Jun 24, 2015 at 18:12

1 Answer 1


A registered (non-spatial) view will be of great benefit to solve this problem:

  1. Create an Oracle view definition (preferably in the schema where the spatial data are stored) which identifies the distinct dates and ranks them (likely earliest to latest.)

         row_number() OVER (order by saledate ASC) as DATE_RANK
         (select distinct sale_date from my_sde_feature_class)
  2. Register this view with SDE and with the Geodatabase from ArcCatalog by right-clicking on it >> Admin >> Register.

  3. Add the registered view to your MXD along with the featureclass

  4. Join the view (now a table object in Desktop) to the featureclass using the shared SALE_DATE column.
  5. Create a query definition on the result where DATE_RANK = 1
  6. Copy the resulting layer three additional times, changing the query definition to DATE_RANK = 2, 3, 4
  7. save often, it is ArcGIS after all

This could also be accomplished by a collection of Spatial View definitions where the logic above is embedded into each spatial view. That way, the geometries and dates are queried, ranked, and selectively-reduced to only make certain geometries display for each layer. There are pros and cons to one approach over the other.

My suggestion allows for the sales data to be separate from the geometry just in case the data gets de-normalized -- (example: same rights might be sold during multiple dates.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.