The view in the MSSQL server not shown in ArcMap

Question

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

New Query Layer.png

The view .png

No Data in the Table.png

features snap.PNG

webmap_question.png

webmap_question2.png

Chang-HengYang · Answer

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

mody_buchbinder · Answer

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

Chang-HengYang · Answer

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)

MAkeQueryTable.jpg