I try to make a point materialized (point) view from a polygon table. First I make a selection on the geometry to make it a point I use SDO_GEOM.SDO_CENTROID on the geometry so I get an extra geometry column 'GEOMETRIE2' which has the centroid of a polygon. [ATTACH=CONFIG]28676[/ATTACH]
After the selection I do an insert on USER_SDO_GEOM_METADATA:
ArcSDE requires that each table have no more than one geometry column. You can register a view on a table with two SDO_GEOMETRY columns, but not the base table itself. Adding a second column to a registered table might make that base table unstable -- You may need to make a second view.
Thanks for the reply. I changed the selection to 1 GEOMETRY column:
SELECT CAST (ROWNUM AS NUMBER (38)) SEQ_ID, SCHW_ID, CMS_ID, CMS_TYPE, CMS_BASE, CMS_TASK, CMS_SD, SDO_GEOM.SDO_CENTROID (c.GEOMETRIE, m.diminfo) GEOMETRIE FROM GWB_MUTATIESIGNALERING_SCHOUW c, user_sdo_geom_metadata m WHERE m.table_name = 'GWB_MUTATIESIGNALERING_SCHOUW' AND m.column_name = 'GEOMETRIE';
And the result (registration and insert to USER_SDO_GEOM_METADATA) worked perfectly.
If you have registered the SEQ_ID field as the registered rowid column, you have generated an unsafe view. It will not function correctly in ArcGIS Desktop or Server because the content of SEQ_ID will change for the same row with for each WHERE clause. Registered rowid (objectid) columns are used to tie the geometries to tables (selection sets), so the value must be the same with each query (rownum does not meet this requirement).