topic Re: Database View Shows Coded Value, not Human Readable? in Data Management Questions

Database View Shows Coded Value, not Human Readable?

Anonymous User — Fri, 10 Jan 2014 21:56:49 GMT

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

Re: Database View Shows Coded Value, not Human Readable?

MarcoBoeringa — Fri, 10 Jan 2014 22:33:56 GMT

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

Since domains, AFAIK, are stored in the Geodatabase System Tables as XML, you won't be able to access them directly and use them in your views.

The "quick and dirty" solution is to export your domains to tables using the Domain To Table tool and than use the resulting tables in a SQL join to modify your "geodatabase views" to display the appropriate "human readable" value.

Assuming you don't update your domains all the time, this should be a manageable workaround once set up.

Re: Database View Shows Coded Value, not Human Readable?

Anonymous User — Mon, 13 Jan 2014 13:57:41 GMT

I appreciate the feedback, thank you. The suggested workaround isn't a good fit for our environment, due to volume and change rate. Since the human-readable values are invoked in every other situation in which a user views the data, it seems like either an oversight or a bug to ignore them in the context of a view. I will follow-up with Esri and will post here with anything learned. Thanks again!

Re: Database View Shows Coded Value, not Human Readable?

MarcoBoeringa — Mon, 13 Jan 2014 16:13:33 GMT

Marianne,

As soon as you start defining true database views, as SQL statements stored at enterprise database level, you are actually leaving the ESRI and geodatabase "realm" of things.

Although it is easy to say that ArcGIS "knows" about these domains in all other situations where geodatabases are accessed through one of the ArcGIS Desktop or Server applications, this is all with ArcGIS "knowing" it is using a geodatabase, not an ordinary database without ArcSDE Repository. Hence ArcGIS can extract the necessary information from the table with XML columns storing the domains (the GDB_ITEMS table of the ArcSDE / Geodatabase Repository). ArcObjects handles this.

When you define a database view, versus doing something like creating a layer with Definition Query in ArcMap, even if it accomplished through ArcGIS for Desktop tools and menu options like the new ones added in the 10.x releases, it is stored and managed by the database. The database doesn't "know" about any of the specifics of the geodatabase model, like where the domain information is stored. It also can not "join" the XML column information about the domains automatically to your base tables. And since ArcGIS sees this same database view as any other ordinary view, it won't be able to extract this info either (unless ESRI deviced some elaborate code to still consult the ArcSDE Repository, even for ordinary database views not registered with the geodatabase).

That is why the export with the Domain To Table tool is necessary.

Of course, ESRI might potentially be able to offer an option to automatically convert domain codes to domain descriptions in a future release by doing an export automatically and modifying the SQL statements on the fly... but this is probably far less easy than it sounds. In the mean time, automating the creation of database views including exported domains using the Domain To Table tools, most likely through Python scripting or ModelBuilder, is probably your best bet.

Re: Database View Shows Coded Value, not Human Readable?

MarcoBoeringa — Mon, 13 Jan 2014 17:00:27 GMT

BTW, I have now verified the viability of the solution I wrote in post #2.

I exported a domain as described there, and than modified both an ArcGIS Query Layer's SQL statement to incorporate the domain's description, and I also tested modifying the underlying database view's SQL (Warning: don't do both at the same time!) to join the exported domain table using SQL Server Management Studio.

Both worked like a charm... the description of the domain nicely replaces the code in both cases.

Re: Database View Shows Coded Value, not Human Readable?

Anonymous User — Mon, 13 Jan 2014 19:15:01 GMT

Thank you, Marco, for the thoughtful response. You clearly describe the distinction between geodatabase and conventional database awareness.

Since Esri enabled ArcGIS Desktop to create a view (as of 10.1, I believe), I would have expected that view to behave as any other geodatabase table behaves i.e. - to be geodatabase aware. But in this case, it's just a button which performs a non-geodatabase function.

Re: Database View Shows Coded Value, not Human Readable?

MarcoBoeringa — Mon, 13 Jan 2014 19:56:17 GMT

Since Esri enabled ArcGIS Desktop to create a view (as of 10.1, I believe), I would have expected that view to behave as any other geodatabase table behaves i.e. - to be geodatabase aware. But in this case, it's just a button which performs a non-geodatabase function.

Exactly!...

Re: Database View Shows Coded Value, not Human Readable?

VinceAngelo — Mon, 13 Jan 2014 19:59:17 GMT

I would have expected that view to behave as any other geodatabase table behaves
i.e. - to be geodatabase aware.

But how could it? It is a database view after all. And the coded values are what a SQL user
would see from the primary table.

The issue is the ancillary data in domains. Should each view be tailored to join to a virtual
table containing the lookup values, in the syntax appropriate to each RDBMS (if the RDBMS
even supports them)? Should it create new tables associated with each domain column each
time a view is created, and join to them? And then what happens if the domain is edited?
Should the command fail if a domain is present?

I suspect this hasn't been resolved because the options are all so unattractive. Yet there is
a workaround -- to export the domains to tables and alter the view accordingly.

- V

Re: Database View Shows Coded Value, not Human Readable?

Anonymous User — Mon, 13 Jan 2014 20:21:08 GMT

Ah, I see where you're all going.

Create a nightly process by which the domain is written to an SDE geodatabase table, then define the view to include the join of that domain table and the corollary feature class/table.

That is a great idea, thanks!

Re: Database View Shows Coded Value, not Human Readable?

Anonymous User — Fri, 13 Aug 2021 13:11:28 GMT

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.

Re: Database View Shows Coded Value, not Human Readable?

DougGreen — Wed, 30 Aug 2023 16:31:54 GMT

That is a good thought. Although, I'd be worried about the domains being disconnected from the tables in case future changes to the domains occur. XML is a queryable datatype within SQL. For example, in SQL Server, there is documentation on how to do it and plenty of examples of how to join that XML to a table in a query. So I would say this should be possible without making static tables of the domains.

Re: Database View Shows Coded Value, not Human Readable?

Bud — Thu, 31 Aug 2023 13:31:46 GMT

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.

Re: Database View Shows Coded Value, not Human Readable?

AJR — Tue, 05 Sep 2023 21:54:20 GMT

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.

Re: Database View Shows Coded Value, not Human Readable?

Wes_S — Tue, 13 Feb 2024 16:58:59 GMT

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?

Re: Database View Shows Coded Value, not Human Readable?

AJR — Tue, 13 Feb 2024 17:15:18 GMT

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.

Re: Database View Shows Coded Value, not Human Readable?

Wes_S — Tue, 13 Feb 2024 19:45:47 GMT

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