The view in the MSSQL server not shown in ArcMap

[Chang-HengYang]

Hi All,

The data in the view created in the MSSQL server 2012 (the attached file, "The view") was not shown when I add the view through Database Connections in  the ArcMap 10.3.1. The window "New Query Layer" pops out when I added the view I mentioned (the attached file, "New Query Layer"). Then I chose the Point ID as the unique identifier (The Point_ID is not the primary key in the view).  However, I can not see anything in the view I just added to the ArcMap (the attached file, "No Data in the Table"). Could anyone provide your solution for this question? How could I add the view in the ArcMap?

Thanks,

Hank

[ModyBuchbinder]

You do not have a geometry/geography field so it will not be visible as a layer.

You should bring it into Arcmap using MakeQueryTable and then use Make X Y Event layer to translate it to point layer

Have fun

Mody

[Chang-HengYang]

Hi Mody,

Thank you for the reply. I tried to follow your instructions and used the MakeQueryTable. However, I am using the more complex query to get the max value (collectiondate)  for a whole row with the group by subquery. The query was listed below. The attached file shows the invalid SQL Syntax. However, in MSSQL Server 201, the syntax works to get the whole row with the max date. I am not sure if the Expression in the MakeQueryTable could work wit the complex query. Could you share your opinions?

Many 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)

[ModyBuchbinder]

I would try to create a view in the database then make this view a query table.

[Chang-HengYang]

Hi Mody,

Thank you for the information. I am wondering if you know query layer could work with the complex query I mentioned in the post.

Thanks,

Hank