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
ON
(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:
Solved! Go to Solution.
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.
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.
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!