SQL error when creating a Query Layer

420
2
Jump to solution
02-19-2021 10:19 AM
Labels (2)
BrianBulla
Regular Contributor

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:

BrianBulla_0-1613758710384.png

 

0 Kudos
1 Solution

Accepted Solutions
MarcoBoeringa
MVP Regular Contributor

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.

View solution in original post

2 Replies
MarcoBoeringa
MVP Regular Contributor

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.

BrianBulla
Regular Contributor

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!

0 Kudos