This content has been marked as final. Show 3 replies
Actually, this is the expected result -- chaos!
One-to-many views are not supported by ArcGIS because it *must* have a unique registered
rowid column (objectid) to tie the table to the geometry. This view would be supported if you
had a unique NOT NULL integer column in the stakholders' table, and specified *that* in lieu
of the non-unique OBJECTID (note a rownum is not permitted, because the value must be
reproducible in subset queries).
Relationship classes are the geodatabase methodology to provide full support for "many"
relationships in ArcGIS.
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.
Yes, secondary queries do probably take a different path through the SQL engine, but if the
registered rowid was unique (as it is required to be), it wouldn't matter.
Some folks try to game the unique requirement by returning row number in the view rowid
column, but since the value is used to refetch the row, this will result in random results without
the previous WHERE constraint (and that's before you consider caching and other optimizer tricks
that could reorder the return sequence).
Since you do have an OBJECTID in LR_Stakeholders, you should use that in the create_view
column list (vice the 'many' one). I've done this extensively with GeoNames data that actually
has distinct places and names (alternate spellings for the same location); so long as you fetch
the rowid from the many table, ArcGIS won't notice that one-to-many isn't supported.