Creating a View in SQL Server Enterprise Geodatabase: Returning Domain and Subtype Descriptions Instead of Codes

[JEMOO-01]

Hello Esri Community,

I’m working with a Feature Class in an Enterprise Geodatabase (SQL Server) that contains fields with domains, subtypes, and attribute rules. I’m trying to create a SQL view that filters records based on a domain field (Status) with values (1, "Active") and (2, "Canceled"). The view should only return records where Status = 1.

So far, I’ve successfully created the view, but the fields with domains and subtypes are returning the codes (e.g., 1 for "Active") instead of the descriptions (e.g., "Active"). I’ve tried various approaches to retrieve the descriptions but haven’t been successful.

Details:

• The domains and subtypes were created in ArcGIS Pro and assigned to fields in the feature class.

• The geodatabase is stored in SQL Server (Enterprise Geodatabase).

• In SQL Server Management Studio (SSMS), I can’t find the domain and subtype information in tables like GDB_ITEMS, GDB_ITEMTYPES, or GDB_ITEMRELATIONSHIPS. However, the descriptions appear correctly in ArcGIS Pro when viewing the feature class attributes.

What I’ve Tried:

1. Creating a SQL view that filters the Status field:

   CREATE VIEW ActiveFeatureClass AS
   SELECT *
   FROM MyFeatureClass
   WHERE Status = 1;

   This works, but the domain and subtype fields return codes instead of descriptions.

2. Attempting to join the GDB_ITEMS table to retrieve domain descriptions, but I couldn’t locate the domain and subtype information in SSMS.

What I Need:

• A way to create a SQL view that returns the descriptions for domain and subtype fields instead of the codes.

• Guidance on how to access domain and subtype information stored in the geodatabase system tables in SQL Server.

Additional Notes:

• The domains and subtypes are managed by the geodatabase and appear correctly in ArcGIS Pro.

• I’m using ArcGIS Pro and SQL Server Management Studio (SSMS).

Any help or guidance would be greatly appreciated! Thank you in advance.

[Robert_LeClair]

I'm using ArcGIS Pro 3.4.2 and a PostgreSQL relational database.  I have a trails feature class with subtypes and domains. In Postgres's equivalent of SSMS, pgadmin, if I go into the gdb_items repository table, under the definition/xml field for the database.owner.TrailsSubset row (FC is in the name column), there's a super long XML field that you have to scroll through to find the subtype code and description.  For the domains, it's another row(s) in the same table.  My domains are called Class Desc, Surface Desc, and Maintenance Desc under the name column.  The codes and description are in the XML column for those row items.  See images below.  But with all this said, not sure how to create the DBView query to access these items...

[JEMOO-01]

Hi Robert_LeClair,

Thank you so much for providing the example and for your help with Viewing the Location of Domains and Subtypes in pgadmin which is the same at SSMS.

i think all is good for extracting domain and creating tables for it so I can extract the code and description separately 
while creating the view there is any way to change field datatype [ having (field 1) short datatype while creating the view can I convert it to text datatype --> while returning the domain description, error will appear cuz it short [ accept only int ]  and I want to return text]  that's why I want to convert field datatype without any change occur in the source feature class   

[Bud]

You could check this out. The Oracle example has worked for me in the past. Hopefully the SQL Server example works for you.

"Example: Resolving domain codes to description values using SQL"

[Bud]

If you run into problems, you could try asking on the Esri Data Management community. There are SQL Server GDB experts over there.

[Asrujit_SenGupta]

You may try using SQL CASE Expression for this simple scenario

SQL CASE Expression

CASE (Transact-SQL)

Sample1: If there is a possibility of Status field having values other than 1/2:

CREATE VIEW ActiveFeatureClass AS
SELECT Field1, Field2, Field3, 
CASE
    WHEN Status = 1 THEN 'Active'
    WHEN Status = 2 THEN 'Canceled'
    ELSE 'Whatever message you want'
	END AS Status, Field4, Field5
FROM MyFeatureClass
WHERE Status = 1;

Sample1: If the Status field has only 1/2 as values, you can use this as well:

CREATE VIEW ActiveFeatureClass AS
SELECT Field1, Field2, Field3, 
CASE
    WHEN Status = 1 THEN 'Active'
    ELSE 'Canceled'
	END AS Status
FROM MyFeatureClass
WHERE Status = 1;