topic Re: SQL error when creating a Query Layer in ArcMap Questions

SQL error when creating a Query Layer

BrianBulla — Fri, 19 Feb 2021 18:19:19 GMT

Hi,

I am trying to create a new Query Layer based on the following SQL Query. In MS Access this query works, but in ArcMap I get an error about a duplicate field name. Can anyone suggest a work-around for this??

Here is my query:

SELECT * from

(SELECT GISWRKS1.works.INSPECTIONS_HYDRANT_INSPECTION.*
FROM GISWRKS1.works.INSPECTIONS_HYDRANT_INSPECTION
WHERE (((GISWRKS1.works.INSPECTIONS_HYDRANT_INSPECTION.DATEWORK) Is Null) AND ((GISWRKS1.works.INSPECTIONS_HYDRANT_INSPECTION.HYDRANT_OPERATION)<>'OK')) OR (((GISWRKS1.works.INSPECTIONS_HYDRANT_INSPECTION.DATEWORK) Is Null) AND ((GISWRKS1.works.INSPECTIONS_HYDRANT_INSPECTION.OPERATING_NUT)<>'OK')) OR (((GISWRKS1.works.INSPECTIONS_HYDRANT_INSPECTION.DATEWORK) Is Null) AND ((GISWRKS1.works.INSPECTIONS_HYDRANT_INSPECTION.NOZZLE_CAPS)<>'OK')) OR (((GISWRKS1.works.INSPECTIONS_HYDRANT_INSPECTION.DATEWORK) Is Null) AND ((GISWRKS1.works.INSPECTIONS_HYDRANT_INSPECTION.FLANGES)<>'OK')) OR (((GISWRKS1.works.INSPECTIONS_HYDRANT_INSPECTION.DATEWORK) Is Null) AND ((GISWRKS1.works.INSPECTIONS_HYDRANT_INSPECTION.WATER_IN_BARREL)='True'))) ALL_WR_Table

INNER JOIN

(SELECT Max(GISWRKS1.works.INSPECTIONS_HYDRANT_INSPECTION.INSP_DATE) AS MaxOfINSP_DATE, GISWRKS1.works.INSPECTIONS_HYDRANT_INSPECTION.FACILITYID
FROM GISWRKS1.works.INSPECTIONS_HYDRANT_INSPECTION
GROUP BY GISWRKS1.works.INSPECTIONS_HYDRANT_INSPECTION.FACILITYID) MOST_RECENT_Table

(ALL_WR_Table.FACILITYID=MOST_RECENT_Table.FACILITYID) AND (ALL_WR_Table.INSP_DATE =MOST_RECENT_Table.MaxOfINSP_DATE)

And here is the error message:

Re: SQL error when creating a Query Layer

MarcoBoeringa — Fri, 19 Feb 2021 19:52:52 GMT

I think you will need to use table name aliases for the two join sections to avoid this. I see it is a self-join, but that doesn't make a difference. This will also make your query much more readable, as you don't need to repeat the whole long concatenation of "GISWRKS1.works.INSPECTIONS_HYDRANT_INSPECTION", but can instead refer to the aliases. The error message suggests SQL Server already attempted to add an alias automatically ('a.'), but since it is self joined table, likely used only the 'a' alias, and not a second one, e.g. 'b', for the table to join.

Re: SQL error when creating a Query Layer

BrianBulla — Fri, 19 Feb 2021 20:56:16 GMT

Thanks Marco. I actually just figured this out too. Since the FACILITYID is coming from two different 'tables' I just needed to specify ALL_WR_Table.FACILITYID (as well as all the other fields).

Yes, I now realize that using an alias would be more helpful too!! 🙂

Thanks for the help!