4

Troubleshooting and debugging ArcMap defintion queries that go beyond a basic single statement can be very frustrating. Statements that work on their own easily break when combined. How can this pain be alleviated?

For example say we want to select all records of last 3 years, and then from that set only the month of march.

In a file-gdb this query selects all records 3 years old and newer:

EXTRACT(YEAR FROM "Date") >= (EXTRACT(YEAR FROM CURRENT_DATE) - 2)

While this query selects the month of March:

EXTRACT(MONTH FROM "Date") = 3

However combining them by inserting year_query in place of 'Date' in the month query will fail with "an invalid SQL statement was used". (On multiple lines for readability, it's all in a single line in query builder):

EXTRACT(MONTH FROM 
   (EXTRACT(YEAR FROM "Date") >= (EXTRACT(YEAR FROM CURRENT_DATE) - 2))
) = 3

For the example the solution is to use AND instead of nesting:

(EXTRACT(YEAR FROM "Date") >= (EXTRACT(YEAR FROM CURRENT_DATE) - 2))
AND (EXTRACT(MONTH FROM Date ) = 3)

but how to get this answer when you don't know it already?

8

"Stack" your definition queries in different layers.

Save each successful query as a separate layer, then use them as input into your next query. On saving the new feature layer ArcMap will combine the queries into a single one that works.

Also keep the SQL reference for query expressions used in ArcGIS handy. The delimiter syntax changes depending on the background DB technology in use, i.e. double quotes versus single quotes vs brackets. Date and time handling are especially variant.

Last 3 years query layer: enter image description here

March from all years query layer: enter image description here

With Make Feature Layer tool, use "Last 3 years" as input layer and put the "March all years" query into the expression field: enter image description here

Result, a working "Only March from last 3 years" layer: enter image description here

  • 1
    For reference, since what you found answers how to fix the issue, but not what the limitations are, here is the reference for query expressions. Feel free to add to your answer. SQL Reference for query expressions – Get Spatial Mar 7 '17 at 18:28
  • @GetSpatial Seems like that should be a separate answer. – Dan C Mar 7 '17 at 18:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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