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?
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
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?
(SELECT POINT_ID, MAX(CollectionDate) AS MaxValue
(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
(t.U IS NOT NULL) AND (t.U > 0)