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

[MarcoBoeringa · Accepted solution]

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.

[DougGreen]

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.

[847396730]

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!

[MarcoBoeringa]

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.

[MarcoBoeringa]

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.

[847396730]

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.

[MarcoBoeringa]

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!...

[VinceAngelo]

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

[847396730]

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!