topic Re: The view in the MSSQL server not shown in ArcMap in ArcGIS Enterprise Questions

The view in the MSSQL server not shown in ArcMap

Chang-HengYang — Mon, 21 Mar 2016 22:55:28 GMT

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

Re: The view in the MSSQL server not shown in ArcMap

ModyBuchbinder — Tue, 22 Mar 2016 05:48:42 GMT

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

Re: The view in the MSSQL server not shown in ArcMap

Chang-HengYang — Tue, 22 Mar 2016 15:05:17 GMT

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)

Re: The view in the MSSQL server not shown in ArcMap

ModyBuchbinder — Wed, 23 Mar 2016 05:43:50 GMT

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

Re: The view in the MSSQL server not shown in ArcMap

Chang-HengYang — Wed, 23 Mar 2016 22:05:29 GMT

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