Database View Shows Coded Value, not Human Readable?

[847396730]

Hello!  When a geodatabase view references a field to which a coded value domain has been applied, the code, rather than the human readable value, is exposed.  Having made extensive use of coded value domains, this makes views useless for my environment.  Does anyone know of a workaround?

Thank you!

ArcSDE1 10.0/SQL Server 2008 R2

[Anonymous User]

Since ArcGIS Enterprise / ArcMap 10.6 and ArcGIS Pro 2.7 (please correct my version numbers if I'm incorrect), the user has had the ability to register database views with the geodatabase. Once I register a database view with the geodatabase, I would expect that it has full geodatabase functionality, including displaying domain descriptions instead of values.

Esri Canada technical support pointed us to this example, which we've tried and it works as expected. However, I would expect that this would be implemented by Esri out-of-the-box.

[Bud]

Related: File Geodatabase views should support domains

Our geodatabase is Oracle, not SQL Server. What I do is create individual views to get the domain values (codes and descriptions) for a given domain.

--This is Oracle-specific SQL. You'll need something different for SQL Server. https://desktop.arcgis.com/en/arcmap/latest/manage-data/using-sql-with-gdbs/example-resolving-domain-codes-to-description-values.htm

create or replace view d_atn_type 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 = 'ATN_TYPE'

That works well for me.

Alternatively, you could create a view that includes the domain codes/descriptions for ALL coded value domains in the geodatabase, or just for a given owner/user/schema. And include the domain name as a column in the view.

Then, when you want to use a specific domain's values in your FC view, you could join to the domain view, but only include the rows that pertain to the applicable domain.

[AJR]

The attached python toolbox might help.  It contains tools to decode domain values using SQL.  The tool is currently SQL Server specific, but could be modified for other db platforms.  Basically, it just builds a big SQL statement with a bunch of case statements in it which translate the domain values to human readable text for use in a view.  There are two tools which use the same backend code.  The first just returns the sql select statement as a string (useful if you are building this into a larger model or tool) and the second uses the select statement to return a query layer.  You can always go back and examine the sql via. the source for the feature layer if needed.  The code works with layers that are subtyped as well as those that aren't.

[Wes_S]

Hello,

I have been playing with your script and I see how it could be very useful. Would you mind explaining the best way to go about implementing this into a enterprise database view in Pro?

[AJR]

You could build a db view using the select statement by just putting it into a create view statement, something like

create view your_new_view_name as

select .....

However, the view is then a static pointer to the domain and subtype values as they existed at the time of creation of the view.  If you ever change any of the domain values or modify the subtypes, you'll need to drop and re-create the view using an updated sql statement.

You could also use the returned select statement to pass into the Create Database View GP tool and that would achieve the same result as doing it via. sql.

[Wes_S]

Thank you for the timely response! It helped me implement it right away.