Debugging definition queries in ArcMap?

Question

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?

Answer 1

"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:

March from all years query layer:

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

Result, a working "Only March from last 3 years" layer: