2

I'm just learning SQL so apologies for the poorly worded question. I'm basically trying to create a spatial view of hydrants that have been painted in 2018. I have a hydrant feature class (wHydrant) and a hydrant maintenance table (wHydrant_Maint). They are related by a common ID, although the names of the fields that contain the common ID are different. Here are my first few lines that I can get to return a view of all my hydrants with maintenance records:

SELECT h.OBJECTID, h.FACILITYID, h.SHAPE  
FROM wHydrant h 
JOIN wHydrant_Maint r
ON h.FACILITYID = r.Hydrant_ID

My issue (well, my issue is that I don’t know or use SQL…) is that I don’t know how to start the next part of the statement. I can do it using the definition query builder, no problem! But creating the proper statement is alluding me. I’m essentially trying to say: wHydrant_Maint.MAINT_ACTIVITY = 'Paint' and MaintDate > '2018’. I thought I could just tack on a WHERE statement, but I think because it’s pulling from the related table that maybe there is additional syntax. I'm using ArcGIS 10.5.1 and a SQL Server Enterprise database.

  • 1
    You can just "tack on a WHERE statement" as you say. Just make sure you refer to the table that holds the information you require, so if the field is in "wHydrant_Maint" you'd use WHERE r.MaintDate > 2018, as you've already specified r as being that table. You can look in both tables in your WHERE statement just by prefixing the h or r as needed for each field. Same in the SELECT statement – Midavalo Apr 4 at 21:58
  • What is the data type of your MaintDate column? Is it of type Date, Integer, or something else? That will affect the syntax of your statement. Also, if you want to select those painted in 2018, you might want to use an '=' sign instead of '>' – FSimardGIS Apr 4 at 22:59
  • The other condition is if you a want 2018 and later in which case the logical test is greater than or equal, symbolized like it sounds >=. Note that the view is not an ArcGIS thing at all, and the RDBMS syntax rules the roost (provided that there is a unique rowid column and geometries of one class point/line/poly or NULL) – Vince Apr 4 at 23:16
  • So I tried adding WHERE and tested each field for results. I can isolate a specific year by using a date range as such: 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') How – Nadar Apr 5 at 17:17
  • However, if I try to query the r.MAINT_ACTIVITY field, it doesn't return anything: 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 = 'Painted' – Nadar Apr 5 at 17:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.