BUG? Complex Queries Not Editable in Clause Mode?

Question

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" {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‌

GIS_Spellblade · Answer

Definitely agree about the limitations of GUI-driven SQL, although there are definitely more products out there pushing the envelope on that front. The issue that I tried documenting above was that outside of clause mode, there are potentially huge SQL limitations that would otherwise be possible in a native SQL environment like SSMS. Having the ability to define things within the definition query makes launching web services easier and gets rid of some of the overhead of having to write those views directly on your database.

I also agree that users should have an option of what to toggle as a preference for the SQL queries, upvoted.

MarcoBoeringa · Answer

Good you got some of these issues logged with ESRI.

I do think however, that some limitations to "Clause Mode" versus "SQL Mode" are logical. It is probably really hard to represent some of the more complex SQL in a sensible way in the "Clause Mode". If you are that much into SQL anyway, why not directly edit in SQL? I personally almost never use "Clause Mode", I much prefer seeing and editing the actual SQL statement. Only rarely do I switch to "Clause Mode". I actually filed an ArcGIS Idea for allowing a preset in the ArcGIS Pro project settings for choosing between Clause or SQL mode. Vote it up if you agree:

Application wide setting / option to switch between "Clause" and "SQL" mode for expressions

GIS_Spellblade · Answer

Bugs! Lots of Bugs! For anyone else that stumbles on this; these bugs are valid as of 2.6.1 BUG-000133709: 'ArcGIS Pro does not validate complex SQL Queries while SQL Server does' OpenDate >= '2020-08-24 00:00:00' AND OpenDate < '2020-09-08 00:00:00' AND (DATEDIFF( Day, OpenDate, CaseInspectionDate) - DATEDIFF(Week, OpenDate, CaseInspectionDate) * 2 <= 1 ) AND CaseReported IN ('Citizen Complaint', 'City Council')‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍ BUG-000133921: 'GETDATE() does not work inside a DATEADD expression for Definition Queries in ArcGIS Pro but validates in SSMS' DateReported > DATEADD(MONTH, -6, GETDATE())‍ BUG-000133922: 'DATEADD expression for Definition Queries fail with closer time-frames to today's date in ArcGIS Pro but validates in SSMS' DateReported > DATEADD(MONTH, -6, '2020/09/17')‍