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

06-05-2013 09:32 AM
Status: Open
Labels (1)
MVP Frequent Contributor

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.


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


Confirmation that they’re not supported:

FAQ: Are Computed/Virtual columns supported in an Enterprise environment in ArcGIS


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);
col column_name format a15
select column_name, hidden_column, virtual_column from user_tab_cols where table_name='T1';

--------------- --- ---
N               NO  NO
SYS_NC00002$    YES YES

select n, SYS_NC00002$ from t1;

              N SYS_NC00002$
--------------- ------------
           -123          123
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?