idea Use SQL for field calculations on versioned data (option to move edits to base) in ArcGIS Enterprise Ideas

Use SQL for field calculations on versioned data (option to move edits to base)

Bud — Wed, 02 Mar 2022 08:31:03 GMT

Currently, in ArcGIS Pro, SQL field calculations are only possible for unversioned EDGB data.

SQL expressions support faster calculations for feature services and enterprise geodatabases. Instead of performing calculations one feature or row at a time, a single request is sent to the server or database, resulting in faster calculations.
Only feature services and enterprise geodatabases support SQL expressions. For other formats, use Python or Arcade expressions.
Using the SQL option for the Expression Type parameter has the following limitations:

- The option is only supported for Db2, Oracle, PostgreSQL, SAP HANA, and SQL Server enterprise geodatabases.
- Calculating field values on joined tables is not supported.
- Versioned and archived data is not supported.
- The ability to undo geoprocessing operations is not supported.

It would be helpful if SQL field calculation support could be expanded to versioned data too.
Specifically, traditional versioning, with the option to move edits to base.

Re: Use SQL for field calculations on versioned data (option to move edits to base)

John_Spence — Thu, 03 Mar 2022 19:05:43 GMT

Agreed!

Re: Use SQL for field calculations on versioned data (option to move edits to base)

Bud — Wed, 19 Apr 2023 04:53:47 GMT

It seems to me that it should be straightforward to implement this on traditional versioned data with the option to move edits to base. Since "the option to move edits to base" makes versioned data essentially behave the same as unversioned data -- just update the base table.

Re: Use SQL for field calculations on versioned data (option to move edits to base)

Bud — Mon, 24 Feb 2025 16:17:43 GMT

@SSWoodward

It sounds like SQL field calculations will be supported for most datatypes in Pro 3.5. Will SQL field calculations be supported for versioned EGDB data too?

Re: Use SQL for field calculations on versioned data (option to move edits to base)

MarceloMarques — Mon, 24 Feb 2025 17:34:00 GMT

@Bud

if the data is registered as versioned (traditional or branch) then the ability to undo is enabled.

if the data is not registered as versioned then undo is disabled, this is by design.

Branch Versioning, editing the data via SQL is not supported, it follows a services-based architecture for viewing and editing data. It facilitates long database transactions using feature services.

SQL Views and Branch Versioning - Esri Community

Traditional Versioning, we can edit the data via SQL using the multi versioned view, see documentation below for more information.

Edit versioned data in Oracle using SQL—ArcMap | Documentation **

**this documentation is only available in the ArcMap docs, Pro works with traditonal version, but customers are being encouraged to move to Pro branch version, the new technology.

If the data is not registered as traditonal version, then of course we can still edit the data, in this case we edit the featureclass base table directly via SQL.

I hope this helps.

Re: Use SQL for field calculations on versioned data (option to move edits to base)

SSWoodward — Tue, 25 Feb 2025 02:19:37 GMT

Hey @Bud

Are you asking specifically about the Calculate Field GP tool?

Re: Use SQL for field calculations on versioned data (option to move edits to base)

Bud — Tue, 25 Feb 2025 13:53:01 GMT

@SSWoodward Yes.

Re: Use SQL for field calculations on versioned data (option to move edits to base)

SSWoodward — Tue, 25 Feb 2025 18:22:25 GMT

Thanks for clarifying, @Bud.

Using SQL expressions on versioned data is not a feature that is included in the enhancements planned for the calculate field GP tool in ArcGIS Pro 3.5

Re: Use SQL for field calculations on versioned data (option to move edits to base)

Bud — Tue, 25 Feb 2025 18:21:55 GMT