topic Joining a spatial layer with Oracle SDE view only returning some records in Data Management Questions

Joining a spatial layer with Oracle SDE view only returning some records

LarryNolan — Wed, 26 Mar 2014 13:14:50 GMT

I'm running SDE 9.1 on Oracle 10g. I have a spatial table with land parcel polygons and the other with some attribute data. The spatial table is indexed but the attribute data table is a view and is not. I do a join in Arcmap based on a parcel id and everything appears to work fine but it does not when you try to use more than a few hundred records.

In ArcMap 10.0, 10.1 and 10.2 only the first 300 records (out of 2300) are returned when the table is viewed.
In ArcMap 9.31 it works fine.

Does anyone know if there settings in ArcMap 10.x that control the number of records returned in a request or is this just an incompatibility between versions with ArcGIS.

Thanks
Larry

Re: Joining a spatial layer with Oracle SDE view only returning some records

LarryNolan — Wed, 26 Mar 2014 13:55:31 GMT

No, all of the records are returned but the attributes for them are blank or null. I can scroll to the end of the 2313 records.

Thanks

I use SQL server, so I'm really not sure. But when you open the attribute table does the count at the bottom have a asterick (beside the count)? (0 out of *300)?

I know that with large tables in Arc 9 and 10 ArcMap does not display all the results only the first 2000, but in your case it seems it should show all. When it only shows a subset of the data you have to click the click the 'Move to End of Table' button towards the bottom left had side of the attribute table.

Perhaps it is a compatibility issue or a bug, hopefully someone familiar with your setup or someone from ESRI will be able to answer your question better.

Re: Joining a spatial layer with Oracle SDE view only returning some records

VinceAngelo — Wed, 26 Mar 2014 13:59:40 GMT

Are you really using ArcSDE 9.1 (which has been retired for years) or
ArcSDE 9.3.1 (which has been retired for months)?

None of the ArcGIS 10.x releases are compatible with ArcSDE 9.1.

In order to find out what's happening, you'd need to provide more
details on the contents of the tables and the exact syntax of the
view.

- V

Re: Joining a spatial layer with Oracle SDE view only returning some records

LarryNolan — Wed, 26 Mar 2014 15:11:43 GMT

Ha, I wish. No it's SDE 9.1, it was in place before the IT beast was created. It's called living in the world where IT controls your environment and it takes forever to change a production system. Every change, no matter how simple is a major project involving teams of people and of course they have no GIS or GIS aware staff so contracts have to be let for SDE work so it never gets done. Sorry for the vent.

I did a little more digging and the joins in ArcMAP 9.31 still have issues and as bad as 10.x but still there. About half the 2313 records are there instead of about one eigth in 10.x

Are you really using ArcSDE 9.1 (which has been retired for years) or
ArcSDE 9.3.1 (which has been retired for months)?

None of the ArcGIS 10.x releases are compatible with ArcSDE 9.1.

In order to find out what's happening, you'd need to provide more
details on the contents of the tables and the exact syntax of the
view.

- V

Re: Joining a spatial layer with Oracle SDE view only returning some records

LarryNolan — Wed, 26 Mar 2014 16:00:11 GMT

Vince,
I have some of the information on the attribute table view "LICENSE" and Spatial Table "CLAIMS"
The views are "not materialized" so they are just plain SDE views.
The view contains attribute data about a land parcel. Owner, issue date, cost and few more similar fields.

Here is the code from the creation of the views.

Thanks again for your help.

CREATE OR REPLACE VIEW LICENSE AS
SELECT l.license_nbr, l.file_nbr AS filenum, c.client_name, l.LOCATION,
          l.num_curr_claims AS numclaims, l.status, l.stake_dt AS stakedate,
          l.record_dt AS recdate, l.issue_dt AS issdate,
          l.wr_due_dt AS wrkdue,
             map_num1
          || ' '
          || map_num2
          || ' '
          || map_num3
          || ' '
          || map_num4
          || ' '
          || map_num5 AS mapsheets,
          w.total_exp
     FROM owners.licenses l, owners.clients c, owners.license_exp w
    WHERE l.client_id = c.client_id and L.LICENSE_ID=W.LICENSE_ID(+) AND status IN ('IS', 'RC', 'P');

-- Create table
create table CLAIMS
(
objectid    NUMBER(38) not null,
cdate       DATE,
operator    VARCHAR2(30),
zone        NUMBER(6),
license_nbr VARCHAR2(10),
shape       INTEGER
)
tablespace LANDPARCELS_SMALL
pctfree 10
initrans 1
maxtrans 255
storage
(
    initial 80K
    next 1M
    minextents 1
    maxextents unlimited
    pctincrease 0
);
-- Create/Recreate indexes
create unique index A90_IX1 on CLAIMS (SHAPE)
tablespace LANDPARCELS_INDX
pctfree 10
initrans 4
maxtrans 255
storage
(
    initial 80K
    next 1M
    minextents 1
    maxextents unlimited
    pctincrease 0
);
create unique index R116_SDE_ROWID_UK on CLAIMS (OBJECTID)
tablespace LANDPARCELS_INDX
pctfree 10
initrans 4
maxtrans 255
storage
(
    initial 80K
    next 1M
    minextents 1
    maxextents unlimited
    pctincrease 0
);
-- Create/Recreate primary, unique and foreign key constraints
alter table CLAIMS
add constraint A90_FK1 foreign key (SHAPE)
references F90 (FID)
disable;
-- Grant/Revoke object privileges
grant select, insert, update, delete on CLAIMS to SDE;
grant select on CLAIMS to VIEWER;

Re: Joining a spatial layer with Oracle SDE view only returning some records

VinceAngelo — Wed, 26 Mar 2014 20:55:51 GMT

What exact version of Oracle are you using (10.a.b.c.d notation)?
Oracle 10.2.0.1.0 was known to not function reliably, and I think
many of the 10.1.* releases had similar issues.

The relationship between the layer and view is 1:Many, correct?
That appears to be your problem -- ArcGIS only supports 1:Many
relationships with relationship classes.

If you want to implement a different view, that joins the layer
in with the other three tables, then you need to:
1) Alter the table with many cardinality so that it has a rowid-ready
column (number(38) NOT NULL, with unique values). NOTE THAT
YOU CANNOT USE ROWNUM() FOR THIS PURPOSE -- EACH
SUBSEQUENT QUERY MUST RETURN THE SAME ROWID VALUE
AS RETURNED BY AN UNBOUNDED QUERY.
2) Create a proxy table that has the same column definitions as
the current view (but with the rowid added)
3) Use 'sdetable -o create_view' to join the layer table to the
proxy table USING THE ROWID COLUMN OF THE PROXY TABLE
INSTEAD OF THE OBJECTID ON THE BUSINESS TABLE, specifying
the "-C" on that rowid column, USER-SET.
4) Edit the resultant view to do a four-table join between the
three view source tables and the business table, using the
columns in the same order as defined in the proxy table.

The resulting view should have unique rowids (you should verify
this with a "SELECT COUNT(DISTINCT rowidcol) FROM viewname"),
or Desktop will not be able to handle the results.

- V