SQL error when creating a Query Layer

BrianBulla

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:

 

MarcoBoeringa

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.