Improve SQL Expression Builder

MarcDyke2

The existing SQL expression builder in ArcGIS Pro is very ponderous to use, especially for people familiar with SQL. Building one expression takes multiple mouse clicks which slows down the process. Unfortunately the 'Edit SQL mode' does not list fields and functions as it did in previous versions of ArcGIS.

Ms suggestions for improvements are:

create an SQL expression builder similar to that which exists for the Label Class expression builder (and existed in previous versions of ArcGIS). It shows the fields that are available and some functions but the user is still free to type in the expression directly.
if a layer or table is selected in the contents window then make that the target of the expression when the tool launches.
enable select by attributes in a context menu on table windows and on layers and tables in the contents window.

wayfaringrob · ‎09-25-2023

Now that I am working with students again (and teaching with Pro for the first time), I am finding that it's very difficult to teach and learn SQL without the ArcMap SQL builder. It was very intuitive, and students could work off of an instruction like this with little to no knowledge of scripting:

In this case, students are working with a classified layer that is normalized by another variable (x/y), and therefore cannot use the dropdowns that the Pro expression builder gives you in its "easy" mode (unless they make and calculate a new field, which they are just now learning to do). They must use SQL, and this was pretty easy to figure out in ArcMap - it's one simple interface, no toggling, you can see what each button does to the expression, which is fantastic for learning: you get a taste of SQL even if you're using the buttons instead of typing. Not only does Pro entirely hide the SQL expression from the user in "easy" mode, but when you switch to the SQL editor, it offers zero help with building an expression for the very first time. It's literally a blank box, and students don't know what to do with this much less that it's there. Interfaces like those in ArcMap or SPSS (just a few examples) are immediately intuitive and helpful for everyone, especially students who may not know where to start with the empty text box that Pro gives you. There was nothing wrong with them; I don't know why they had to be messed with. They were purpose-built, functionality-focused, and fine-tuned. I cannot say the same of Pro.

I feel that now I have to provide a whole tutorial on SQL, since syntax errors are much easier to commit in the Pro editor vs. ArcMap. When I learned GIS (in ArcMap) and first started TAing (also ArcMap), this wasn't really necessary. The only thing we really had to worry about was making sure students had a space between operators and repeated their field clauses. Now, we have to teach the language from scratch since little help is provided by the interface. (I'm actually going to have them use ArcMap in the next class so I can at least get them some practice.) I am begging Esri to make this more usable by reverting to the ArcMap expression builder.

Pro's expression builder has just been a horrible step backwards all around; the opening lines of this suggestion (thank you @MarcDyke2) hits the nail on the head:

The existing SQL expression builder in ArcGIS Pro is very ponderous to use, especially for people familiar with SQL. Building one expression takes multiple mouse clicks which slows down the process. Unfortunately the 'Edit SQL mode' does not list fields and functions as it did in previous versions of ArcGIS.

As did this comment:

I find the intellisense distracting and it does not provide any visual clues for field names and the like until you have already started typing.

This idea is 5 years old now; here's to hoping something can be done about this horrible yet common interface before another 5 pass.

AlfredBaldenweck · ‎09-28-2023

The weird thing is that the implementation of various features we want is all over the place. We have them in some workflows but not others.

Query Layers show all the fields, but no operators nor unique values.

Definition query builder shows what we want for user-friendly GUI part, but SQL mode is completely blank.

Creating a view? Good luck. You gotta know your datasets very well before you can successfully write the query. It's SQL mode from the definition queries, but you can't even switch back to GUI mode to get you started.

ArcMap's SQL editor was pretty perfect. The GUI mode for definition queries in Pro is great for beginners or quick queries, but SQL mode throws you into the deep end. Where are my lists of fields and unique values? Where are my operators? Where is Pro automatically adding the correct delimiters to field names?

wayfaringrob · ‎10-13-2023

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

AndreaB_ · ‎10-20-2023

Good idea!!

Waan · ‎12-05-2023

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 · ‎12-05-2023

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 · ‎01-02-2024

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.