Solved! Go to Solution.
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.
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.
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.
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?
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.
Thank you for the timely response! It helped me implement it right away.