topic BUG? Complex Queries Not Editable in Clause Mode? in ArcGIS Pro Questions

BUG? Complex Queries Not Editable in Clause Mode?

GIS_Spellblade — Sat, 11 Dec 2021 14:13:52 GMT

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‌

Re: BUG? Complex Queries Not Editable in Clause Mode?

GIS_Spellblade — Mon, 24 Aug 2020 18:14:11 GMT

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?

Re: BUG? Complex Queries Not Editable in Clause Mode?

GIS_Spellblade — Sat, 11 Dec 2021 14:13:54 GMT

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')‍

Re: BUG? Complex Queries Not Editable in Clause Mode?

MarcoBoeringa — Thu, 17 Sep 2020 17:15:14 GMT

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

Re: BUG? Complex Queries Not Editable in Clause Mode?

GIS_Spellblade — Thu, 17 Sep 2020 17:59:40 GMT

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.