Consistent/visible SQL expressions and query definitions in layers
Details:
Make Query Table - allows for joining tables (is a spatial feature layer, unlike Make Table View which is a standalone table)
The SQL expression is hidden. It's not a definition query.
Data is not editable.
I saved the layer as a .lyrx file. The SQL expression is stored as a "whereClause" in the JSON source.
{
"type" : "CIMLayerDocument",
"version" : "3.2.0",
"build" : 49743,
"layers" : [
"CIMPATH=map/querytable.json"
],
"layerDefinitions" : [
{
"type" : "CIMFeatureLayer",
"name" : "QueryTable",
"uRI" : "CIMPATH=map/querytable.json",
"sourceModifiedTime" : {
"type" : "TimeInstant"
},
"useSourceMetadata" : true,
"description" : "main.QueryTable",
"layerElevation" : {
"type" : "CIMLayerElevationSurface",
"elevationSurfaceLayerURI" : "CIMPATH=Map/8534c3b1c33b44cbac3bcdf2d05bc946.json"
},
"expanded" : true,
"layerType" : "Operational",
"showLegends" : true,
"visibility" : true,
"displayCacheType" : "Permanent",
"maxDisplayCacheAge" : 5,
"showPopups" : true,
"serviceLayerID" : -1,
"refreshRate" : -1,
"refreshRateUnit" : "esriTimeUnitsSeconds",
"blendingMode" : "Alpha",
"allowDrapingOnIntegratedMesh" : true,
"autoGenerateFeatureTemplates" : true,
"featureElevationExpression" : "0",
"featureTable" : {
"type" : "CIMFeatureTable",
"displayField" : "type",
"editable" : true,
"dataConnection" : {
"type" : "CIMTableQueryNameDataConnection",
"workspaceConnectionString" : "DATABASE=main;SERVER=C:;INSTANCE=sde:sqlite:C:\\2023 Files\\GDBs10\\New Mobile Geodatabase.geodatabase;DB_CONNECTION_PROPERTIES=C:\\2023 Files\\GDBs10\\New Mobile Geodatabase.geodatabase;IS_GEODATABASE=true;AUTHENTICATION_MODE=OSA;IS_NOSERVER=0",
"workspaceFactory" : "SQLite",
"dataset" : "main.QueryTable",
"datasetType" : "esriDTFeatureClass",
"tables" : "main.A_TEST_FC",
"whereClause" : "type = 'b'",
"shapeType" : "esriGeometryPolygon",
"featureType" : "esriFTSimple",
"shapeFieldName" : "Shape"
...Unrelated, but worth mentioning: Make Query Table - Article ID:000010848 Create a one-to-many join in ArcMap
Make Table View (is a non-spatial standalone table, unlike Make Query Table which is a feature layer)
The SQL expression is a definition query.
Data is editable.
The SQL expression is a definition query.
Data is editable.
Make Query Layer (ArcGIS Pro 2.6.8; Oracle 18c 10.7.1 EGDB)
The SQL definition (full-blown SELECT query) is stored in the Query section in the Properties —> Source tab.
Data is not editable.
Only enterprise geodatabases support query layers, not mobile or file geodatabases.
Create Database View (mobile geodatabase)
The SQL definition (full-blown SELECT query) is displayed in the Catalog item properties —> Definition tab.
Data is not editable.
The Query section in the screenshot below isn't actually editable; it brings up the Edit Query Layer dialog, but the dialog isn’t useable/functional. See: Mobile geodatabase query layers. File geodatabase views do not behave the same way; they don’t have a Query section in the Source tab in the Properties window.
Unlike the other properties window screenshots above, this is the Catalog item properties, not the Contents feature layer properties:
SQL formatting (line breaks and indents) are not preserved when the view is created using ArcGIS Pro. Whereas query layers, definition queries, and Select by Attributes do display/preserve SQL formatting without issue.
Selections in .LYRX files, .MAPX files, etc.:
They seem to be different yet again. There is no SQL expression in the JSON; there is only a binary reference to those ObjectIDs.
- How are selections stored in a .MAPX map file?
- .LYRX and .MAPX files — Store selection SQL expression in file's JSON source
- Display selection criteria (SQL) in the Layer Properties window
The "subset" information is stored in a "Selection definition set" in the Properties —> Selection tab.
Data is editable.
Related: