Consistent .exp SQL expression loading/parsing in GP tools

Bud — Thu, 04 Jul 2024 19:30:37 GMT

I have a .exp SQL expression file that has the following SQL in it:

type = 'b'

If I load the .exp SQL expression into the tools listed in the SQL Expression documentation, most of the tools will automatically parse the SQL into the "clause mode" UI picklists.

For example, Select By Attributes tool:

However, the Make Query Table tool doesn't seem to parse the SQL into the UI picklists:

Could the tools be standardized so that loaded .exp files are consistently parsed into "clause mode"? (It looks like the Make Query Table tool is the only tool that needs to be enhanced.)

Also, could my other notes in bold be considered?

SQL reference for query expressions used in ArcGIS:
Within ArcGIS Pro, the SQL expression dialog box [it's not a 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.
Doesn't support .exp files or have an expression dialog.
Related: Use SQL query expression as filter in Calculate Field GP Tool
Use Select to query data for further analysis.
Use the Make Query Table geoprocessing tool to create a layer or table view.
Doesn't parse the SQL into the "clause mode" UI picklists, unlike the other tools in this list.
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.
Doesn't support .exp files or have an expression dialog.
I think this tool should either be removed from the list (since it doesn't have an expression dialog), or the Make Query Layer tool should be added to the list, since it is similar.
Use the Append geoprocessing tool to append multiple input datasets into a target dataset.
Use ProSDK Core.Data.QueryDef.

These tools are missing from the list:
Make Query Layer
Make Table View
Table Select
Calculate Fields (multiple)

Re: Consistent .exp SQL expression loading/parsing in GP tools

Bud — Mon, 08 Jan 2024 07:15:13 GMT

Some of the following tools have SQL expression functionality, but are not included in the list of GP tools above.

Should any of these tools be added to the list above? Most are likely too obscure, but there could be some more generic tools that could be added, such as Make Table View, Table Select, and Calculate Fields (multiple).

MakeQueryLayer_management query
AddLocations_na search_query
CalculateLocations_na search_query

AddAttributeRule_management script_expression
AlterAttributeRule_management script_expression
~~Append_management expression~~
AppendData_geoanalytics append_expressions
CalculateField_gapro expression
CalculateField_geoanalytics expression
~~CalculateField_management expression~~
~~CalculateField_management expression_type~~
CalculateFields_management expression_type
CalculateValue_mb expression
ConnectPublicTransitDataModelToStreets_transit expression
CreateBuffers_gapro buffer_expression
CreateBuffers_geoanalytics buffer_expression
CreateColorComposite_ia blue_expression
CreateColorComposite_ia green_expression
CreateColorComposite_ia red_expression
CreateColorComposite_management blue_expression
CreateColorComposite_management green_expression
CreateColorComposite_management red_expression
CreateParcelRecords_parcel record_expression
ExportReportToPDF_management expression
ExpressionIfThenElse_mb expression
FindFrequentedLocations_intelligence expression
GenerateDriveTimeTradeArea_ba expression
GenerateThresholdDriveTimeTradeArea_ba expression
GenerateThresholdRingTradeArea_ba expression
GenerateTradeAreaRings_ba expression
GenerateUnitOpenings_indoors door_detail_expression
GenerateUnitOpenings_indoors wall_detail_expression
RasterCalculator_ia expression
RasterCalculator_sa expression
ReconstructTracks_gapro buffer_expression
ReconstructTracks_gapro split_expression
ReconstructTracks_geoanalytics buffer_expression
ReconstructTracks_geoanalytics split_expression
UpdateBDCDatasetProperties_gapro expression

AddCollapseContainerByAttributeRule_nd where_clause
AddDiagramFeatureCapabilityByAttributeRule_nd where_clause
AddExpandContainerByAttributeRule_nd where_clause
AddImagesToOrientedImageryDataset_oi where_clause
AddReduceEdgeByAttributeRule_nd where_clause
AddReduceJunctionByAttributeRule_nd where_clause
AddRemoveFeatureByAttributeRule_nd where_clause
AddSetRootJunctionByAttributeRule_nd where_clause
AddSetStartingPointByAttributeRule_nd where_clause
AddSpatialQueryRule_nd added_where_clause
AddSpatialQueryRule_nd existing_where_clause
AnalyzeMosaicDataset_management where_clause
BuildBoundary_management where_clause
BuildFootprints_management where_clause
BuildMosaicDatasetItemCache_management where_clause
BuildOverviews_management where_clause
BuildPyramidsandStatistics_management where_clause
BuildSeamlines_management where_clause
CalculateCellSizeRanges_management where_clause
ComputeDepthMap_management where_clause
ComputeDepthMap_rm where_clause
ComputeDirtyArea_management where_clause
ComputeFiducials_management where_clause
Con_ia where_clause
Con_sa where_clause
CreateOverpass_cartography where_clause
CreateReferencedMosaicDataset_management where_clause
CreateUnderpass_cartography where_clause
DefineMosaicDatasetNoData_management where_clause
DownloadRasters_management where_clause
Eliminate_management ex_where_clause
~~ExportFeatures_conversion where_clause~~
ExportMosaicDatasetGeometry_management where_clause
ExportMosaicDatasetItems_management where_clause
ExportMosaicDatasetPaths_management where_clause
~~ExportTable_conversion where_clause~~
ExtractByAttributes_sa where_clause
ExtractDataByFeature_topographic where_clause
FieldValueIsIfThenElse_mb invert_where_clause
FieldValueIsIfThenElse_mb where_clause
~~MakeFeatureLayer_management where_clause~~
MakeImageServerLayer_management where_clause
MakeMosaicLayer_management where_clause
~~MakeQueryTable_management where_clause~~
MakeRasterLayer_management where_clause
MakeTableView_management where_clause
MergeMosaicDatasetItems_management where_clause
MosaicDatasetToMobileMosaicDataset_management where_clause
RematchAddresses_geocoding in_where_clause
RemoveDepthMap_management where_clause
RemoveDepthMap_rm where_clause
RemoveRastersFromMosaicDataset_management where_clause
RepairMosaicDatasetPaths_management where_clause
RepairTrajectoryDatasetPaths_management where_clause
~~Select_analysis where_clause~~
~~SelectLayerByAttribute_management invert_where_clause~~
~~SelectLayerByAttribute_management where_clause~~
SetNull_ia where_clause
SetNull_sa where_clause
SplitMosaicDatasetItems_management where_clause
SynchronizeMosaicDataset_management where_clause
TableSelect_analysis where_clause
Test_ia where_clause
Test_sa where_clause
UpdateFeaturesWithIncidentRecords_ca where_clause
UpdateInteriorOrientation_management where_clause

~~CreateDatabaseView_management view_definition~~

Where did I get the lists above? I searched for these keywords in parameter names:

query, expr, where, view_definition

Using the following Python script:

for tool in arcpy.ListTools("*"):
    params = arcpy.GetParameterInfo(tool)
    for param in params:
        if "query" in param.name:
            print(tool, param.name)

Source: List of all parameter names of all geoprocessing tools in ArcGIS Pro

idea Consistent .exp SQL expression loading/parsing in GP tools in ArcGIS Pro Ideas

Consistent .exp SQL expression loading/parsing in GP tools

Re: Consistent .exp SQL expression loading/parsing in GP tools