Consolidate Select by Attribute Expression Modes

[Gerald_Daumiller]

When the ArcGIS Pro "Select Layer By Attribute" window is first presented, you must select how you want to proceed before you can start building an expression.  Depending on your choice the window either goes to a mode with an expression builder interface (but where the expression you are building is difficult to see) or to a SQL mode that lets you directly type a SQL expression, but has few tools for helping you build it correctly.

The ArcMap "Select By Attributes" window operates in a single mode that contains all of the functions of both modes of the ArcGIS Pro window.  My idea is to add the SQL expression pane to the ArcGIS Pro query builder window. This would eliminate the need to initially select the mode, make it easier to see the full expression in query builder mode, and make help from the query builder widgets availabe while you type in the SQL expression pane.

Also I think the "Select Layer By Attribute" window title should be changed to "Select By Attributes".  The tool is NOT for selecting a layer, it is for selecting features from a layer. 

The revised ArcGIS Pro window would look like this:

As you build an expression, the SQL box would remain below the clause-builder widgets.

Doing this would make the initial expression-building decision, shown below, unnecessary. The new initial window includes all of the functionality of the first two choices, and the "Add expression from file" option is available with the Load button.

The graphics below show what the window looks like in its two modes in ArcGIS Pro 2.4.2.  If you are looking at the widgets, you cannot see what the current expression is.  If you are looking at the SQL pane, it does have intellisense to help you pick fields for the expression, but the drop-down list for field values is not there to help you enter them with the correct syntax.  The required "timestamp" keyword and the single quotes in the SQL expression below right were provided by selecting the time value from the drop-down list on the left.  You probably wouldn't know how to enter it this way if you did not have help from the interface.

 

You can't switch to the expression builder for help from the SQL pane if it contains a partial expression:

For reference, the ArcMap tool for this is below.  Everything is available in one window. You can alternate between the SQL expression pane and the clause building widgets with impunity.  The ArcMap tool is less concerned about whether a valid SQL statement is being formed than the ArcGIS Pro window is.  The ArcGIS Pro window might also need a Verify button and to stop worrying about whether a valid statement exists while the widgets are being manipulated. 

 

[wayfaringrob]

@AlfredBaldenweck  entirely agree. This is all I'm asking for.

[AndreaB_]

Good idea!!

[Waan]

Adding my 'kudos' to this idea ... and also echoing the previous person who mentioned taking a screenshot of the fields to help when writing straight SQL. It's been five years since this enhancement was requested--could we get an update?

[AlfredBaldenweck]

Also, something I've noticed since moving to 3.X-- the intellisense/autocomplete is entirely too sensitive. I keep trying to do something like 

select
a.*
from exampleSDE\user.example_tbl a
where ObjectID > 500

and as soon as I type in "a" and a period (or even just "a" by itself, e.g. in the from clause), it autocorrects to "ABS()". 

I do not want this. Please calm it down a little bit. 

[Bud]

Related: SQL reference for query expressions used in ArcGIS

In ArcGIS Pro, the SQL expression dialog box can be found in the following locations:

• Select by attributes using the Select Layer By Attribute geoprocessing tool.
• Definition Query tab on the Layer Properties dialog box.
• Display filters tab in the Symbology pane.
• Create reports using the Create New Reports pane.
• Export tables using the Export Table geoprocessing tool.
• Export features using the Export Features geoprocessing tool.
• Use the Calculate Field geoprocessing tool to create an expression to perform simple or complex calculations on field values.
• Use Select to query data for further analysis.
• Use the Make Query Table geoprocessing tool to create a layer or table view.
• Use the Make Feature Layer geoprocessing tool to create a feature layer.
• Create a view in a database or geodatabase using the Create Database View geoprocessing tool.
• Use the Append geoprocessing tool to append multiple input datasets into a target dataset.
• Use ProSDK Core.Data.QueryDef.

[wayfaringrob]

@TanuHoque

Re:

the initial landing page --> I'm pretty sure you know that it is removed a few releases ago. I'm just adding this for any future references.

Again, the implementation on this is inconsistent and incomplete. It *does* still pop up in different parts of the interface.

[AlfredBaldenweck]

Another thing to add to the wishlist here: Line numbers (and, if possible, error messages that tell you what line the error is on. I realize SQL doesn't care about lines, but if you tell me there's an error near a comma, I need to know which of the literal hundred commas in my query you're talking about.)

And again, the intellisense is entirely too sensitive.