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

Question

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:

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

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

Asrujit_SenGupta · Answer

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;

Bud · Answer

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

Bud · Answer

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"