Is additional SQL syntax necessary to account for a field with a domain/coded values?

Question

I'm attempting to create a spatial view. The following statement returns an empty view, even though there are "Flushed" records in the MAINT_ACTIVITY field.

 SELECT h.OBJECTID, h.FACILITYID, h.SHAPE, r.Hydrant_ID, r.MAINT_ACTIVITY, r.MaintDate, r.GlobalID
 FROM wHydrant h
 JOIN wHydrant_Maint r
 ON h.FACILITYID = r.Hydrant_ID
 WHERE r.MAINT_ACTIVITY = 'Flushed'

If I remove the last WHERE clause, the query will return a view of all my hydrants. If I change the WHERE clause to query another field, it will return correct results. I'm only having issues with this specific MAINT_ACTIVITY field and the only major difference is that this field has a domain with coded values. However, the coded values match the descriptions. So, Flushed is the code and Flushed is the description. Is additional syntax still necessary? Why doesn't the above statement work?

*Update--When I query other fields in the Hydrant_Maint table, all the MAINT_ACTIVITY field results return as NULL, even though there are entries in the original table.

• Andy: It's not totally clear from your post but if you have all NULLs in the wHydrantMaint columns after the join, this indicates a problem with your join. Are h.FACILITYID and r.Hydrant_ID both the same data type?
• Nadar: No, only the MAINT_ACTIVITY field results are null. All other fields in the wHydrant_Maint table return the correct entries. However to answer your question, yes, the join fields are both text types.

Answer 1

The SQL query in my question was actually correct. Upon viewing the base table in SSMS, I saw that all the MAINT_ACTIVITY entries were indeed NULL. Upon looking at the Views version of the table, I could see all my entries. So, because the table was registered as versioned, I just needed to change the query to search the view table, not the base table. The following returned the spatial view I was looking for:

SELECT h.OBJECTID, h.FACILITYID, h.SHAPE, r.Hydrant_ID, r.MAINT_ACTIVITY, 
r.MaintDate, r.GlobalID
FROM wHydrant h
JOIN wHydrant_Maint_evw r
ON h.FACILITYID = r.Hydrant_ID
WHERE (r.MaintDate >= '2014' AND r.MaintDate <= '2015') AND r.MAINT_ACTIVITY = 'Flushed'