Spatial View has features but the table is empty

Question

I've created a spatial view that displays features in ArcCatalog and ArcMap but if I look at the table, it's empty, though the table does have the correct column names.  If I identify a feature, it returns results including the attributes.

Things of note about the spatial view is that a majority of the fields come from another view, which is registered with the geodatabase with sdetable -o register command.

It's an Oracle 11G database with SDE 9.3.1 on it.

Any suggestions?

Thanks,

Craig

VinceAngelo · Answer

'sdetable -o create_view' should only really be used with SDEBINARY/SDELOB storage layers.
SQL is a much more powerful tool for view creation than what 'sdetable' provides.

For ArcGIS to operate correctly, 1:M joins need to return the same USER-set rowid value for the
same row with each query (no fast/loose games with ROWNUM).  It's not well-enforced, but the 
column is required to map to SE_INT32_TYPE (long integer). Use 'sdetable -o describe' on the
view to make sure the type mapping is correct.

The segmentation violations are disconcerting, as is the use of DBLINK, which will often perform, 
shall we say, "poorly" (make sure you tune the query so that selection is occurring on the correct
side of the link).

That join where clause isn't at all pretty -- you might want to evaluate using a UNION ALL join
within the view (just make sure the returned rowids are unique).

- V

CraigPatterson · Answer

Thanks for your reply.Does the view contain a registered rowid (objectid) column?

Yes it does, the view alias it's unique number id as objectid.  I register it with this command:

sdetable -o register -t OPEN_SR_VW -c OBJECTID -C USER -S Is the join 1:1, 1:M, or M:1?

The join is a 1:M.  What does 'sdetable -o describe' report for each of the tables?

It says "segmentation fault" for both.  The view is created via a database link.  That may be causing the error message.What geometry storage is being used?

ST_GEOMETRYDid you define the table with 'sdetable -o create_view' or did you use CREATE VIEW with
'sdelayer -o register'?

See belowWhat exact command did you use to create the view?

sdetable -o create_view -T OPEN_SR_SVW -t   "OPEN_SR_VW SR, MXCENTROID" -c "SR.OBJECTID, SR.MXTICKETID, SR.MXCLASS, SR.MXORGID, SR.DESCRIPTION, SR.STATUS, SR.LEAD, SR.OWNER, SR.INTERNALPRIORITY, SR.REPORTDATE, SR.COMMODITY, SR.COMMODITYGROUP, SR.FAILURECODE, SR.PROBLEMCODE, SR.SUPERVISOR, SR.REPORTEDBY, SR.REPORTEDPHONE, SR.REPORTEDPRIORITY, SR.REPTBYCONTACTNAME, SR.SHOP, SR.CHANGEDATE, SR.MXSITEID, SR.PLUSSFEATURECLASS, SR.MXASSETNUM, SR.MXLOCATION, SR.MXADDRESSCODE, MXCENTROID.SHAPE" -w "(SR.mxticketid = mxcentroid.maxgisid and sr.mxclass = mxcentroid.maxgisid2 and mxcentroid.maxtablename = 'SR') OR (SR.mxassetnum = mxcentroid.maxgisid and SR.mxsiteid = mxcentroid.maxgisid2 and mxcentroid.maxtablename = 'ASSET' AND SR.PLUSSFEATURECLASS IS NULL) OR (SR.mxlocation = mxcentroid.maxgisid and SR.mxsiteid = mxcentroid.maxgisid2 and mxcentroid.maxtablename = 'LOCATIONS' AND SR.PLUSSFEATURECLASS IS NULL AND SR.MXASSETNUM IS NULL) OR (SR.mxaddresscode = mxcentroid.maxgisid  and mxcentroid.maxtablename = 'PLUSSSERVICEADDRESS' AND SR.PLUSSFEATURECLASS IS NULL AND SR.MXASSETNUM IS NULL AND SR.MXLOCATION IS NULL)"  -i sde:oracle11g:maxdevgis -u maxspatial

VinceAngelo · Answer

Does the view contain a registered rowid (objectid) column?

Is the join 1:1, 1:M, or M:1?

What does 'sdetable -o describe' report for each of the tables?

What geometry storage is being used?

Did you define the table with 'sdetable -o create_view' or did you use CREATE VIEW with
'sdelayer -o register'?

What exact command did you use to create the view?

- V