Spatial View, one to many relationships, unexpected results
I have a created a spatial view from the following input: poly fc (unique ParcelID) & attribute table (not unique ParcelID resulting in a one 2 many relationship).
sdetable -o create_view -T LR_Land_Owners_SV_noOLWands -t LR_Land_Owners,LR_Stakeholders -c "LR_Land_Owners.OBJECTID,LR_Land_Owners.PARCELID,LR_Land_Owners.Shape,LR_Stakeholders.NATURE,LR_Stakeholders.Stakeholder,LR_Stakeholders.Frequency,LR_Stakeholders.multi_stakeholders,LR_Stakeholders.order_limits" -a "OBJECTID,PARCELID,SHAPE,NATURE,STAKEHOLDER,FREQUENCY,MULTI_STAKEHOLDERS,ORDER_LIMITS" -w "LR_Land_Owners.PARCELID = LR_Stakeholders.PARCELID AND LR_Stakeholders.Stakeholder = 'London Borough of Wandsworth'" -u XXXXX -p XXXXX -i sde:sqlserver:database_instance -D LTTGISV
The view is created as i would expect, the results are as i would expect whereby i get a list of stakeholders as defined by above.
However, the issue being that when i review the data in ArcGIS desktop, whereby i run a defn query against the stakeholder attribute, I get values for the stackholder attribute that SHOULD not be listed. I can understand why its happening (the join on PARCELID is resulting in first 'random' record where the one2many relationship exist). Interestingly if you run query against the attribute table, it will only let see the unique records (for stakeholder) as defined (restricted) by your query defn. Would one agree its a bug?
The issue is avoided if i put the stakeholder query within the spatial view query definition but I'm trying to avoid this since this results in unique spatial views for each of my stakeholders (whereas if i keep the stakeholder query in the mxd (query definition) it enables me to have a single 'stakeholder' spatial view).
I note 'identifying' the parcels in question seems to always return the correct value for stakeholder; as does my web app which is dependent on the spatial view.
I have played with the join type in the view (database level) but with no success.
Interested in any feedback anyone has to give as while i think i understand what is happening, it seems someone inconsistent.
Re: Spatial View, one to many relationships, unexpected results
Vince thanks for prompt response.
While i appreciate your comments Vince & agree partially, i still cannot help but think the sequence in which the order of the sql statement being run is varying between that which makes up the 'table view' in the desktop (seems that the defn query is fired after the spatial view is read), and that of an attribute query (or identify) via desktop upon which the firing the sql defn & spatial view is being considered as a single statement which will return an expected result (fyi parcelID & stakeholder for the join table results in a unique value; hence this is why if the spatial view includes the sql where statement for the stakeholder (e.g. AND LR_Stakeholders.Stakeholder = 'London Borough of Wandsworth'") you end up with as expected results.
I can appreciate unexpected behaviour but i would expect it to be consistent (contradicting myself!) between desktop functionality (if you get what i am saying). I'm not saying this is necessarily a bug, nor by design, but i suspect the underlying sql statements are NOT taking the same path (order) to return the end results & hence the inconsistency.
One thing to note, the SV defn i provided, i inadvertently included the Stakeholder query (AND LR_Stakeholders.Stakeholder = 'London Borough of Wandsworth'") to make the record set for the join table unique. This isn't an issue, its just this SV gives consistent results.
W.R.T. your comments, are you inferring the join be based upon an objectid (i should think not (for obvious reasons) but i don't quite understand your statement re *must* have unique registered rowid). Both tables in the spatial view are gdb registered with each having objectids. You go on further to highlight *that* in lieu of the non-unique OBJECTID - can you elaborate a little more.
Appreciate i'm obviously at an advantage of having greater understanding of my data than yourself.
Tags for this Thread