Database View in ArcGIS 10.1

[DavidMajor]

Hi,

I have 10.1 enterprise geodatabase on SQL Server 2008 with a polygon layer (Parcel) in a feature dataset and a table (Zoning_tb). I have made a view and brought it into ArcGIS Desktop 10.1. I'm getting some strange behaviors...

- When I identify a single polygon it returns about 100 records

- I'm trying to input the layer into the dissolve tool, and dissolve based on 3 attributes. It does not return a completely dissolved layer. I have to then run the tool again on the output to get something closer.

- I did a unique value symbol by the attribute Zone and it gives the same value a different color, but does not show that value twice in the table of contents.

It asks about a unique field when I load it so I'm wondering if my query does not  have the correct fields. Here is my query to make the view.

CREATE VIEW Zoning_view AS SELECT Parcel.OBJECTID, Parcel.Shape, Zoning_tb.IDPARCEL, Zoning_tb.IDOTHER, Zoning_tb.ROLL, Zoning_tb.ZONE, Zoning_tb.ELECT, Zoning_tb.BYLAW
FROM Parcel RIGHT JOIN Zoning_tb ON Parcel.IDPARCEL = Zoning_tb.IDPARCEL
WHERE (((Zoning_tb.IDPARCEL) Is Not Null) AND ((Zoning_tb.ZONE) Is Not Null))

Thx,
David

[ShannonShields · Accepted solution]

David,

I think that this has something to do with the cardinality of your join, and that Desktop is probably taking the Parcel ObjectID field as the layer's unique identifier, when it might not actually be unique.

If there are many zoning records to one parcel, then the Parcel ObjectID field in the view will not be unique. If this is the field that ArcMap is assuming is a unique id field, then you'll end up with some unexpected behavior. Desktop requires a unique identifier field for many operations, such as labelling.

If you go to the layer properties, Source tab and click Change Query you'll get to the Edit Query Layer dialog. Check the 'Show Advanced Options' checkbox and click Next. On the advanced part of the dialog you'll see a list of all the Unique Identifier candidates - try choosing a candidate from the Zoning_tb table. Making sure that the field chosen for the unique identifier truly is unique will solve some of the issues you are seeing.

As for identifying returning 100 records, it could be the same issue - if there are 100 zoning records that relate to a single polygon, the view will actually return that polygon 100 times - one for each of the zoning records.

-Shannon