Hi!
So at my office I curate a lot of datasets maintained by other (ArcGIS-compatible, but not ArcGIS-using) departments, fed into an Oracle-based SDE, for consumption by all departments in an intranet web mapping system used by all departments and all divisions at my company.
One of the groups of data I have contains two feature classes of point data that are functionally very similar, and for an unrelated project it looked like I needed both FCs merged into one FC, so I tried to accomplish this using a union in a Spatial View:
(Table names have been changed and some fields removed for anonymity's sake)
CREATE OR REPLACE FORCE VIEW POINTS_COMBINED
(OBJECTID, SHAPE)
AS
SELECT CAST (ROWNUM AS INTEGER) AS OBJECTID, SHAPE
FROM
(
SELECT SHAPE FROM POINTS_1
UNION ALL
SELECT SHAPE FROM POINTS_2
)
ORDER BY OBJECTID;
This seems to work perfectly - the total number of features in the Spatial View is the same as the sum of the features in the individual FCs.
With this, I decided to try the same method with a couple of polygon FCs, but for whatever reason I get the error "Stream Not Found" when I use literally the same SQL statement with the FC names changed to reflect the polygon FCs.
Any idea what I'm missing? I've checked the Spatial Reference for both and they're the same, so it's not that, as far as I can tell. Is it possibly just that polygon shape data is more complex than point shape data?
Solved! Go to Solution.
You can't use ROWNUM as a registered rowid. The value MUST be repeatable on subsequent
queries, or the graphic to table relationship will be random.
- V
You can't use ROWNUM as a registered rowid. The value MUST be repeatable on subsequent
queries, or the graphic to table relationship will be random.
- V
Is there another way to create a unique non-nullable integer field in the view using an expression, then? Most of the solutions I found by googling around online just suggested using the ROWNUM on the container level.
No, I don't think it's possible to construct a UNION view without unique rowid values in the source tables. It's far easier to tear apart a single table than to join multiple tables in this manner (and the performance would be better as well).
-V
I was afraid of that. Oh, well. It's not too hard to just add another GP to my update scripts to accomplish the same thing; I was just mostly trying this out as an experiment.
Thank you for the help!
Hi V
I am running into the same problem.
If there is a field (for example uKey) that is unique between the two tables (my responsibility to manage it) can I use it as OBJECTID?
Should I register my view into the geodatabase once it created?
Thanks
Mody