Virtual Attributes (ad hoc)

[Bud]

We need virtual, ad hoc SQL columns in the attribute table.

Think of a field calculation, but it’s a dynamic, temporary, read-only mechanism used for one-off tabular data analysis. Takes advantage of the power of SQL; should be as easy to set up as definition queries.

The field would be set up in the feature layer properties, not as a persistent field or as a persistent object in the database. Allows editing FC fields in the same attribute table. Avoids the need to create a clunky query layer or view and then join it back to the FC. No exclusive locks or privileges required other than SELECT.

Examples:

1. case when class='A' then 1 else 0 end as class_flag
   • Sort the  class_flag  field as descending (and sort other fields too). Is a workaround for things like Promote Selected Records.
2. Quickly get the total cost from a related table (1:M) using a subquery:
   (select sum(cost) from budget b where p.id=b.id and year=2025)
   
   • One-time criteria that doesn't justify a permanent field, database view, query layer, etc.
   • I want to see the total right in the FC attribute table. I don't want to use a relate to point to a related table.
3. case when last_edited_date is not null then 1 else 0 end as null_flag
   • Sort  null_flag  and  last_edited_date  descending. Used to specify how nulls are sorted in the attribute table (nulls first or last).
4. Lots of other scenarios.
   • From LukePinner in a comment — Using read-only corporate data: concatenate two or more fields, split a field, change the case of some text values, etc. For example, relate via a composite ID field.

[SSWoodward]

I've moved it back to open while this Idea gets looked into further.  Thanks for clarifying. 

[Luke_Pinner]

Yes, this is a very different idea to "Link - Allow support for virtual / computed fields in Oracle/SQL Server versioned geodatabases" or native database functionality.  It's more akin to storing label/popup definitions in a layer (lyrx/aprx map layer) in that the virtual fields would be properties of the layer, not the data source and could be generated on the fly using Arcade or python expressions.

A simple use case that I constantly run up against - data source: read-only corporate data. I need to do some calcs on an attribute (concat two or more fields, split a field, change case of some text values, etc.) e.g for using in symbology. I know  I can manually change symbology entries or group them, but that's manual and tedious and I know I can export the table or feature class to a copy that I can edit, but that's an issue for data management.  And query layers are fiddly and don't work on non-GDB datasources.

[Bud]

@Luke_Pinner 

Well said! 
This really does feel like a gap in core ArcGIS technology. I think Esri needs to keep in mind that the data analysis industry is a very fast-paced business. We can’t afford the time or messiness that comes with the classic workarounds you described. We need a clean, efficient, read-only way to do dynamic analysis on attributes. (While still having the option to edit FC fields in the same attribute table, if needed.)
But I don’t think that functionality exists yet.

[wayfaringrob]

Exactly @Luke_Pinner  and @Bud!

[MarceloMarques]

You can create virtual column in the RDBMS as an alternative solution.

ORACLE-BASE - Virtual Columns in Oracle Database 11g Release 1

SQL Language Reference (oracle.com) - Note on Oracle Virtual Columns

Specify Computed Columns in a Table - SQL Server | Microsoft Learn

PostgreSQL: Documentation: 15: 5.3. Generated Columns

I hope this helps.

[Bud]

Related to the original idea: Add ability to create arcade expression fields in attribute tables in Pro. (Like it can be done in p...

I think SQL would be orders of magnitude faster than Arcade. I suspect that's why arcade expression fields weren't implemented for attribute tables. The performance would have been very poor for anything but the smallest of tables.

[Bud]

A related performance tip about using CASE in Oracle SQL -- from Esri Case #03356933:

We've been looking specifically into using the CASE statement in Oracle views and people  state that they encounter performance issues.  I found some users suggesting to use DECODE instead of CASE. The view I created is below. The performance greatly improved on sorting. Maybe this can be an alternative for the user. The performance went from 50 seconds to sort the flag column to 13 seconds.

create or replace view SDE.TEST_TABLE_DECODE_VW as
select objectid, PRIMARYOID,
DECODE(type,'NT',1)flag
from SDE.test_table2

Here is the forum thread that suggested using DECODE:

https://blog.toadworld.com/2018/01/26/oracle-coding-best-practices-making-a-case-for-the-case-statem...

@MarceloMarques might find that interesting.

[MarceloMarques]

@Bud - Indeed. Some or the newer Oracle SQL Functions might not have the same performance as the Old functions, I was aware of the CASE and DECODE case because I have seen this in the past and I am certain there are a lot of other interesting cases, writing good SQL statements is always a challenge. Thanks for sharing with everyone. 🙂

[Bud]