Spatial View does not preserve Subtype and Domain Fields

Question

Hello There,

I created a spatial view from a Feature class and a Standalone table. The view works fine with data and shape.
But the domain attributes/columns no more have domains related to them, and the subtype attribute is no more recognized as a subtype field. Both fields are changed to normal fields in the view.
Kindly share your thoughts to fix this.

Environment: ArcGIS Pro 3.0, ArcCatalog 10.8.1, SQL Server 2019, Geodatabase 11.0.0.3.0

Thanks,

Bud · Answer

What kind of database is it?

If it's Oracle, then you could use the SQL from this post for coded value domains: Select domain codes/descriptions.

--create or replace view d_all_coded_value_domains_vw as 
select      
cast(rownum as number(38,0)) as rownum_,
    x.code,
    x.description,
    i.name as domain_name
from        
    sde.gdb_items_vw i
cross apply xmltable(
'/GPCodedValueDomain2/CodedValues/CodedValue' 
    passing xmltype(i.definition)
    columns
        code        varchar2(255) path './Code',
        description varchar2(255) path './Name'
    ) x    
where      
    i.name is not null --https://gis.stackexchange.com/questions/434701/find-problem-domains-in-gdb-items-vw

And this post for subtypes: https://gis.stackexchange.com/a/434583/62572

--create or replace view sub_lc_events_asset_class_activity_vw as (
select 
    x.subtype_code,
    x.subtype_description,
    x.subtype_field,
    x.subtype_field_domain,
    i.name as table_name
from   
    sde.gdb_items_vw i
cross apply xmltable(
    '/DETableInfo/Subtypes/Subtype/FieldInfos/SubtypeFieldInfo[FieldName="ACTIVITY"]' 
    passing xmltype(i.definition)
    columns
        subtype_code         number(38,0)  path './../../SubtypeCode',
        subtype_description  varchar2(255) path './../../SubtypeName',
        subtype_field        varchar2(255) path './FieldName',
        subtype_field_domain varchar2(255) path './DomainName'
    ) x
where  
    i.name is not null
    and i.name = 'INFRASTR.LC_EVENTS'

Or use the sample Oracle or SQL Server queries here:

Example: Resolving domain codes to description values using SQL

VinceAngelo · Answer

Since it's already a view, you can populate lookups to map the integer domain values to the appropriate values.

- V

George Thompson · Answer

I think that this would be expected. Domains / Subtypes are a geodatabase function that the DB does not understand.

Look at this post for some ideas: https://community.esri.com/t5/arcgis-pro-questions/create-database-view/td-p/1122781