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

508
3
06-05-2013 09:32 AM
Status: Open
Labels (1)
BillFox
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.

3 Comments
AntonSizo_

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

Bud
by

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

Bud
by


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
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?