Create Database View

155
3
12-05-2021 04:33 AM
Labels (2)
MohammedElsayed
New Contributor III

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')

Tags (2)
0 Kudos
3 Replies
jcarlson
MVP Frequent Contributor

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.

- Josh Carlson
Kendall County GIS
MohammedElsayed
New Contributor III

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

0 Kudos
Asrujit_SenGupta
MVP Regular Contributor
0 Kudos