Definition Query: Let me add an overarching clause with togglable secondary clauses

AlfredBaldenweck

I frequently run into a problem where I want to toggle between two parts of a query or turn part of the query off.

For example:

{Select * from table where} OFFICE_CODE = 'OFC_001' AND RoadTYPE = "Paved"

As I analyze, I realize that I actually need to see all the Roads for a brief moment.

My options are:

Edit the Query to remove the second clause
Add a new definition query that is just OFFICE_CODE = 'OFC_001'

#1 is not ideal because then I'm going to have to edit it in again, and #2 is not ideal because now I have an extra query floating around that isn't doing anything meaningfully different.

Another scenario is

{Select * from table where} OFFICE_CODE = 'OFC_001'
    AND (RoadTYPE IN ('Paved', 'Gravel')
         AND Road_MPH > 50
         )
    )

But I realize that I also need to sometimes see

{Select * from table where} OFFICE_CODE = 'OFC_001'
    AND (RoadTYPE = 'Two-Track'
         AND Road_MPH < 50
         )
    )

In short, the first clause didn't change at all.

What I'd like to be able to do is add a toggle to other clauses to let me switch between them more easily.

Here is a mock-up of what I have in mind. I only went down one level, but I think it could be cool to branch it down a little farther.

In this example, I always need to see the interstates, but the other attributes are going to change depending on where I am in the workflow.

I think @Bud might appreciate this idea

Bud

Related idea: Single-line SQL comments in definition queries

TanuHoque

@AlfredBaldenweck

Thanks for the idea.

I was wondering if your requirements can be met with the multiple definition queries that we support.

You need to add two definition queries to your layer.

one with

OFFICE_CODE = 'OFC_001' AND RoadTYPE = "Paved"

and, another with

OFFICE_CODE = 'OFC_001'

and you can toggle between them from the Definition Query drop down on the Data contextual ribbon tab, or from the layer's Properties page

AlfredBaldenweck

Thanks for the quick response, Tanu.

Unfortunately, that is what I'm trying to avoid.

My options are:
Edit the Query to remove the second clause
Add a new definition query that is just OFFICE_CODE = 'OFC_001'
...#2 is not ideal because now I have an extra query floating around that isn't doing anything meaningfully different.

The aim for this Idea is to be able to just turn of the parts of the query that I don't currently need, rather than making a whole new query or editing it and then having to change it back.

(This might be another Idea to be made, but why can't we duplicate definition queries with a button click or something? If that's the intended workflow for this, it's not very user-friendly)

In short, I think each clause (or group) should be able to be turned off and on as needed. @Bud's related Idea mentions commenting out different parts in order to make this work. While I'm not sure that should necessarily be the mechanic to accomplish this, that's essentially what we'd be doing here and is a good way to illustrate it.

The mock-up in the original post only operates on one level:

-- Swapping out part of the query
select * from table 
where road_marker = 'INT'
     AND 
         (SURFACE_TYPE = 'Aggregate'
          AND ENTITY_LABEL = 700204)
     -- SPEC_DESIG = 'Historical Route'

It also implies that I can either have that group clause about Surface type and entity label active OR I can have the clause about Special Designation active. I'm not sure if this should be a radio button situation or a checkbox situation. That is, are we swapping different parts of the query in and out with each other or are we turning parts on and off?

-- Turning off part of the query
select * from table 
where road_marker = 'INT'
     AND (SURFACE_TYPE = 'Aggregate'
          AND ENTITY_LABEL = 700204)
     -- AND SPEC_DESIG = 'Historical Route'

Honestly, I'm fine with either.

The other thing is that my mockup only went down one level. That is, in the group clause, you can either have the group clause, or you can't, but you can't turn off part of it. I'm not sure if being able to turn off part of the group is necessary, but it could also be nice.

-- Turning off part of the group clause?
select * from table 
where road_marker = 'INT'
     AND (SURFACE_TYPE = 'Aggregate'
          --AND ENTITY_LABEL = 700204
          )
     -- AND SPEC_DESIG = 'Historical Route'

Where I'm going with this is that typically the first clause of the definition query (and of a grouped clause, too) is the most important one. I think that being able to turn off or switch the other clauses so you can focus on the important parts as necessary without having to duplicate your definition queries would be helpful.

To be honest, I almost never use the query switcher on the Data tab; I always go to the layer's properties. That being said, I don't think there would be a need to change the Switcher's behavior-- since we'd essentially be editing the definition query, it would make sense to only be able to do this on the Properties window. You're not changing to a different query; you're just editing it for a minute before switching it back.

Bud

I wonder if you could use query layer parameters in some cases.

TanuHoque

thanks @AlfredBaldenweck for providing details of your workflow. We will discuss and see what we can do about this.

as for your ask about "but why can't we duplicate definition queries with a button click or something", there is an existing Idea. Please add your comment in there and up vote it. The idea is under consideration.

https://community.esri.com/t5/arcgis-pro-ideas/duplicate-definition-query/idi-p/1197818

thanks

Tanu