topic Re: Create Database View in ArcGIS Pro Questions

Create Database View

MohammedElsayed — Sun, 05 Dec 2021 12:34:25 GMT

I am trying to create a database view using the SQL statement but I have a coded domain within my data.

How can I display the coded value instead of the code in the View.

This is the statement i use:

SELECT OBJECTID, SURVEY_ID_REF AS 'Survey ID Ref', SAMPLE_NAME AS 'Sample Name', SAMPLE_DESC AS 'Sample Description', SYMBOL AS 'Sample Type', SAMPLING_DATE AS 'Sampling Date', Shape, LAT AS 'Lat', LONG AS 'Long', LOCAL_EASTING AS 'Easting(m)', LOCAL_NORTHING AS 'Northing(m)', PENETRATION AS 'Penetration', RECOVERY AS 'Recovery', SURVEY_NAME AS 'Survey Name', CLIENT_NAME AS 'Client Name', COUNTRY_NAME AS 'Country Name', SURVEY_AREA_NAME AS 'Survey Area Name', SURVEY_PROJECT_GEODESY AS 'Project Geodesy', WATER_DEPTH AS 'Water Depth', FGS_Report_URL AS 'Report URL', FGS_Data_URL AS 'Data URL'

FROM MEI_Geotech_U1.GEOTECHNICAL_SAMPLE

WHERE (COUNTRY_NAME = N'EG')

Re: Create Database View

jcarlson — Sun, 05 Dec 2021 18:52:42 GMT

The specifics will vary between database platforms (MSSQL, PostgreSQL, MySQL, etc.), but domains tend to be stored in an informational schema in your database, and are not part of the table you're querying.

In order to return the domain codes, you'll need to query the table where your domains are kept, then find the domain in question, then pull out and parse the domain codes/values, and then join those into your table based on the domain value.

I'd attempt a specific example, but without knowing what DB platform you're on, it would probably not be very useful to you.

Re: Create Database View

MohammedElsayed — Sun, 05 Dec 2021 20:52:55 GMT

Thanks @jcarlson for your reply, I am using MSSQL.

Re: Create Database View

Asrujit_SenGupta — Mon, 06 Dec 2021 12:41:44 GMT

This may help you:

Example: Resolving domain codes to description values using SQL

Here is another workaround for you:

Database View Shows Coded Value, not Human Readable?