BUG? Complex Queries Not Editable in Clause Mode?
I've noticed that some queries seem to be too complex within the SQL Expression Editor; previously I had issues running a calculation from a datefield that would not work on anything less than 6 months. Here's an example that I'm running into today:
I have a map that needs to be updated bi-weekly, it's based on the following query:
2 week range >= and >
a diff that calculates one business day turnaround
according to two complaint flags
OpenDate >= '2020-07-27 00:00:00' And OpenDate < '2020-08-10 00:00:00'
AND (DATEDIFF(Day, OpenDate, CaseInspectionDate) - DATEDIFF(Week, OpenDate, CaseInspectionDate) * 2 <= 1 )
AND CaseReported IN ('Citizen Complaint', 'City Council')Then when I go to make a simple edit, changing the string for date:
OpenDate >= '2020-08-10 00:00:00' And OpenDate < '2020-08-24 00:00:00'
AND (DATEDIFF(Day, OpenDate, CaseInspectionDate) - DATEDIFF(Week, OpenDate, CaseInspectionDate) * 2 <= 1 )
AND CaseReported IN ('Citizen Complaint', 'City Council')It flips out like so:
Pretty horrible right?
Is this a bug?
Now if I were limited to the GUI that would be bad. So I went ahead and used the new mp module in ArcPy, because I'm in the process of automating this map anyway:
import arcpy
old_query01 = "OpenDate >= '2020-07-27 00:00:00' And OpenDate < '2020-08-10 00:00:00' AND (DATEDIFF(Day, OpenDate, CaseInspectionDate) - DATEDIFF(Week, OpenDate, CaseInspectionDate) * 2 <= 1 ) AND CaseReported IN ('Citizen Complaint', 'City Council')"
old_query02 = "OpenDate >= '2020-07-27 00:00:00' And OpenDate < '2020-08-10 00:00:00'"
old_query03 = "OpenDate >= '2020-07-27 00:00:00' And OpenDate < '2020-08-10 00:00:00' AND CaseReported IN ('Citizen Complaint', 'City Council')"
new_query01 = "OpenDate >= '2020-08-10 00:00:00' And OpenDate < '2020-08-24 00:00:00' AND (DATEDIFF(Day, OpenDate, CaseInspectionDate) - DATEDIFF(Week, OpenDate, CaseInspectionDate) * 2 <= 1 ) AND CaseReported IN ('Citizen Complaint', 'City Council')"
new_query02 = "OpenDate >= '2020-08-10 00:00:00' And OpenDate < '2020-08-24 00:00:00'"
new_query03 = "OpenDate >= '2020-08-10 00:00:00' And OpenDate < '2020-08-24 00:00:00' AND CaseReported IN ('Citizen Complaint', 'City Council')"
aprx = arcpy.mp.ArcGISProject(r"C:\Users\gisspellblade\Desktop\ArcProjects\SolidWaste_EnergovDemo.aprx")
for m in aprx.listMaps():
print(f"Map: {m} Layers")
for lyr in m.listLayers():
print(f"\t{lyr.name}")
if lyr.supports("DEFINITIONQUERY"):
if lyr.name == "Complaint Response One Business Day:":
lyr.definitionQuery = new_query01
print(f"Defintion changed for {lyr.name}: {lyr.definitionQuery}")
if lyr.name == "Cose Total: ":
lyr.definitionQuery = new_query02
print(f"Defintion changed for {lyr.name}: {lyr.definitionQuery}")
if lyr.name == "Complaint Total: ":
lyr.definitionQuery = new_query03
print(f"Defintion changed for {lyr.name}: {lyr.definitionQuery}")
aprx.save()Which at first blush, also did nothing.
I ran this several times with no resulting changes in the map.
I restarted the program and still nothing.
Then, I left the program closed for more than 10 minutes, and when I get back, I have these results:
So now I have three questions:
- If I'm changing the value on lyr.definitionQuery, then I'd think it would be an over-write and not an append? and if lyr.definitionQuery is a read/write; how does one access multiple queries to appropriately delete/add what they need?
- the documentation here is pretty light; Alphabetical list of arcpy.mp classes—ArcGIS Pro | Documentation unless there's more detailed information lurking just past my level of google-fu
- Why are these values not showing up immediately within the project? and why do I need a long cold restart to view changes made with ArcPy?
- this is an issue that I've seen before of needing to close a project for a ArcPy-derived result to appear in previous Pro versions
- What's with the GUI not accepting changes to complex queries, or refusing to write them in the first place?
In ArcGIS Pro 2.6
arcgispro2.6 arcpy #definition expressions #set definition #sql clause #sql query builder
edit---
let's define complex query as a query that cannot be represented in clause mode; in the steps below I referred to it as a lock, which is not the case. That text lets the user know that it can only offer the SQL screen and not the Clause screen.
edit---
And for one final bit of mystery, after removing the "old" query from each layer, only the queries that can be directly interpreted by the SQL Builder were kept. Removing one of the complex queries removed both of them. I can re-create the old one, and it is accepted, but when I alter the date it rejects it.
Stuff that works in the builder
Now deconstructing the complex one:
- It will build correctly with the old query
- Accepted, and will show
- When you click edit, it shows a clause that will not edit
- So let's assume that the above is a lock, it'll unlock if you reduce the complexity; here we keep the date math and drop the flag check, which works
- But it will not unlock for a similar level of complexity, nor allow an incremental adding of complexity
- And when you try to write in a query exactly similar except for the date range
Super odd, no?
