idea Allow support for virtual / computed fields in Oracle/SQL Server versioned geodatabases in Data Management Ideas

Allow support for virtual / computed fields in Oracle/SQL Server versioned geodatabases

BillFox — Fri, 09 Sep 2022 21:13:02 GMT

Allow support for virtual / computed fields in Oracle/SQL Server versioned geodatabases.

This could allow the GIS user to symbolize from old to no with graduated symbols based on date fields.
The GIS user should be able to calculate the date field as an epoch date to be used for symbols color ramp and the GIS user should not need DBA security to modify a central geodatabase schema.
And, still allow versioned editing, compression to state zero, etc. as usual.

Support computed columns in SDE

AntonSizo_ — Tue, 13 Apr 2021 16:12:07 GMT

It would be really great and handy if SDE could support computed columns.

Re: Allow support for virtual / computed fields in Oracle/SQL Server versioned geodatabases

Bud — Mon, 16 May 2022 10:56:05 GMT

Confirmation that they’re not supported:

FAQ: Are Computed/Virtual columns supported in an Enterprise environment in ArcGIS
https://support.esri.com/en/technical-article/000008488

Re: Allow support for virtual / computed fields in Oracle/SQL Server versioned geodatabases

Bud — Tue, 17 May 2022 15:53:31 GMT

On a related note, I thought this was interesting (Oracle):

If you create a function-based index, a virtual column is added implicitly.

create table t1(n number);
create index i1 on t1(abs(n));
insert into t1 values (-123);
commit;
col column_name format a15
select column_name, hidden_column, virtual_column from user_tab_cols where table_name='T1';

COLUMN_NAME     HID VIR
--------------- --- ---
N               NO  NO
SYS_NC00002$    YES YES

select n, SYS_NC00002$ from t1;

              N SYS_NC00002$
--------------- ------------
           -123          123

https://community.oracle.com/tech/developers/discussion/comment/16835670#Comment_16835670

I know from experience that function-based indexes don’t break ArcGIS: How To: Create Oracle Spatial function-based indexing

I wonder if that means we could create a hidden virtual column (via an SQL client), and ArcGIS wouldn’t have a problem with it, because it’s hidden? I imagine the only way you could see the column in ArcGIS is via a query layer or a view. So that’s not totally ideal. And the automatic column name isn’t very user-friendly. Although I suppose we could give it an alias in the query.

Ultimately, I’m not sure how useful that is. We can just do the calculation in a view or query layer. A hidden virtual column doesn’t have any advantage. But it’s interesting to think about.

Hidden virtual column created by FBI: Is the column name stable?