topic Re: Why is a spatial view returning empty or null records when field has a domain? in Geodatabase Questions

Why is a spatial view returning empty or null records when field has a domain?

JacobLozano — Mon, 08 Apr 2019 18:32:39 GMT

I'm attempting to create a spatial view between a feature class (wHydrant) and a related table (wHydrant_Maint). I can successfully create views by querying every field in the Hydrant_Maint table, except one (Maint_Activity). The only difference from the other fields is that this Maint_Activity field has a domain. Any attempt to create a view with a WHERE clause on this field will return an empty view with no records returned. When querying other fields, but including this field in the selection, all the results in this field return as null, even though there are in fact entries. Here is my SQL statement that returns all NULL 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.MaintDate >= '2014' AND r.MaintDate <= '2015'

The following returns an empty table view:

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'

Do I need to adjust my syntax to account for a domained field? Also, the code and description for the domain match, in case you're thinking I should query the code for 'Flushed'.

Re: Why is a spatial view returning empty or null records when field has a domain?

George_Thompson — Mon, 08 Apr 2019 22:19:06 GMT

I am not sure that you can add domains (as you see them in a client) to the spatial view as they are specific to advanced geodatabase functionality; https://desktop.arcgis.com/en/arcmap/latest/manage-data/geodatabases/registering-a-table-with-the-geodatabase.htm

You may be able to do it if you specify the domain value(i.e. - 0,1,2,3,4,5, etc.) that is stored in the GDB base table.

Re: Why is a spatial view returning empty or null records when field has a domain?

Asrujit_SenGupta — Tue, 09 Apr 2019 06:45:08 GMT

Is the data Versioned? Maybe you need to perform a Compress, to push edits from the a- & d- Tables into the Business Table (base Table).

Or, create the Spatial View based on the Versioned View of that feature class.

Re: Why is a spatial view returning empty or null records when field has a domain?

JacobLozano — Tue, 09 Apr 2019 14:46:23 GMT

The code value and the description are the same for this domain. We did not use numeric codes in this instance. However, I went into SSMS to view the wHydrant_Maint table records and all of the MAINT_ACTIVITY records are NULL. Yet when the table is viewed in Arc I see all the entries. So if SQL doesn't see the entries, how am I supposed to query them in SQL? Also, I noticed that all the fields with domains are NULL when viewed in SSMS, BUT there is also a "NOTES" field, a text field with no domain, that is also all NULL in SQL.

Re: Why is a spatial view returning empty or null records when field has a domain?

JacobLozano — Tue, 09 Apr 2019 14:55:38 GMT

The data is registered as versioned. When I view the System View table in SQL I can now see my entries, whereas all entries are NULL when I view the base table. I will test querying this table and report back my results.

Re: Why is a spatial view returning empty or null records when field has a domain?

JacobLozano — Tue, 09 Apr 2019 15:16:55 GMT

The queries in my original question are actually correct and were returning the correct results as the MAINT_ACTIVITY field in the base table was actually all NULL records. Since the table was registered as versioned, the records I wanted to query were in the Views folder of SSMS. So the following query returned the results that I have been looking for via a spatial view:

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'

Thank you George & Asrujit for guiding me to the base & view tables in SSMS.