AnsweredAssumed Answered

The complex query in query layer

Question asked by chyang0107 on Apr 8, 2016
Latest reply on Apr 12, 2016 by vangelo-esristaff

Hi all,

 

Thank you for your time in advance. I am wondering if any tool or layer in the ArcGIS could work with the SQL query directly. In my previous post, Mody Buchbinder mentioned the MakeQueryTable might work with the complex query listed below. However, it did not work. Now, I was told that the Query layer might work with the complex query. Or you would suggest to execute the complex query in the MSSQL server to get the data and export the data to ArcMap.

 

Let me know your valuable opinions. 

Thanks,

Hank

 

 

SELECT

        t.POINT_ID,

          t.CollectionDate,

          t.HistoricDate,

          t.U,

          t.LAT,

          t.LONG,

          t.WellDepth,

          t.DataSource

FROM

                      (SELECT POINT_ID, MAX(CollectionDate) AS MaxValue

                       FROM

                                    NM_Water_Quality.dbo.MASTER_UNION_Water_Quality

                       WHERE     

                                 (U IS NOT NULL) AND (U > 0)

                                 GROUP BY POINT_ID) AS x INNER JOIN

                                 NM_Water_Quality.dbo.MASTER_UNION_Water_Quality AS t ON x.POINT_ID = t.POINT_ID AND x.MaxValue = t.CollectionDate

WHERE     

            (t.U IS NOT NULL) AND (t.U > 0)

Outcomes