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?