I'm using Oracle v10.2.0.4, and I have spatial view that works fine as long as I don't define 'select distinct' in the view sql. My view was created first by creating a simple view with a subset of columns from just my spatial layer:sdetable -o create_view -T VW_ERA_POINTS -t GIS_FP_POINTS
-c "OBJECTID,PK,NAME,TYPECODE,LAT,LON,SHAPE"
I then modified the view SQL as follows:CREATE OR REPLACE FORCE VIEW "SDE"."VW_ERA_POINTS" ("OBJECTID", "PK", "NAME", "APP_USER_SEQ", "TYPECODE", "LAT", "LON", "SHAPE")
AS
SELECT
"GFP"."OBJECTID",
"GFP"."PK",
"GFP"."NAME",
"EAU"."USER_SEQ" as "APP_USER_SEQ",
"GFP"."TYPECODE",
"GFP"."LAT",
"GFP"."LON",
"GFP"."SHAPE"
FROM
SDE.APP_USER_ERA_ENV_INT_GROUP EAU,
SDE.GIS_FP_POINTS GFP,
SDE.ENV_INT FEI,
SDE.ENV_INT_TYPE EIT,
SDE.ENV_INT_GROUP EIG
WHERE
EAU.ERA_ENV_INT_GROUP_SEQ = EIG.ERA_ENV_INT_GROUP_SEQ
AND EIG.ERA_ENV_INT_GROUP_SEQ = EIT.ERA_ENV_INT_GROUP_SEQ
AND EIT.ENV_INT_TYPE_SEQ = FEI.ENV_INT_TYPE_SEQ
AND GFP.PK = FEI.FACILITY_SEQ
AND GFP.ISFAC = 'Y';
This works fine and I can view the view in ArcMap, etc. but returns duplicate rows because of the table relationships. If I change 'SELECT ...' to 'SELECT DISTINCT...', it reports the following error:ORA-22901: cannot compare nested table or VARRAY or LOB attributes of an object type
22901. 00000 - "cannot compare nested table or VARRAY or LOB attributes of an object type"
*Cause: Comparison of nested table or VARRAY or LOB attributes of an
object type was attempted in the absence of a MAP or ORDER
method.
*Action: define a MAP or ORDER method for the object type.
And in fact it does the same when I run the SELECT outside of the view, i.e w/o the 'CREATE OR REPLACE FORCE VIEW...'.From what I've read on this KB article, it looks like it may be having a problem with the SHAPE column, but if I change '"GFP"."SHAPE" to 'TO_CHAR("GFP"."SHAPE")' per that article, then it throws a different error:ORA-00932: inconsistent datatypes: expected NUMBER got SDE.ST_GEOMETRY
00932. 00000 - "inconsistent datatypes: expected %s got %s"
*Cause:
*Action:
My table describes, etc. are as follows (can't do 'describe long' since it exceeds 10,000 char limit of post):>sdetable -o describe -t GIS_FP_POINTS -- my spatial layer w/ SHAPE
ArcSDE 10.0 for Oracle10g Build 1343 Thu Feb 17 11:45:42 2011
Attribute Administration Utility
-----------------------------------------------------
Table GIS_FP_POINTS:
Column name Attribute type Null? Length,DPs RowID Column?
-------------------------------------------------------------------------------
OBJECTID SE_INT32 NOT NULL 10 SDE Set
PK SE_NSTRING NOT NULL 254
NAME SE_NSTRING NULL 254
PRGM SE_NSTRING NULL 254
PGRMPK SE_NSTRING NULL 254
LAT SE_FLOAT64 NULL 38,8
LON SE_FLOAT64 NULL 38,8
COL SE_NSTRING NULL 254
TYPECODE SE_NSTRING NULL 254
ISFAC SE_NSTRING NULL 254
COORD_ID SE_NSTRING NULL 254
SHAPE SE_SHAPE NULL 0
>sdetable -o describe -t APP_USER_ERA_ENV_INT_GROUP
ArcSDE 10.0 for Oracle10g Build 1343 Thu Feb 17 11:45:42 2011
Attribute Administration Utility
-----------------------------------------------------
Table APP_USER_ERA_ENV_INT_GROUP:
Column name Attribute type Null? Length,DPs RowID Column?
-------------------------------------------------------------------------------
APP_USER_ENV_INT_GROUP_SEQ SE_INT32 NOT NULL 10
USER_SEQ SE_INT32 NOT NULL 10
ERA_ENV_INT_GROUP_SEQ SE_INT32 NOT NULL 10
OBJECTID SE_INT32 NOT NULL 10 SDE Set
>sdetable -o describe -t ENV_INT
ArcSDE 10.0 for Oracle10g Build 1343 Thu Feb 17 11:45:42 2011
Attribute Administration Utility
-----------------------------------------------------
Table ENV_INT:
Column name Attribute type Null? Length,DPs RowID Column?
-------------------------------------------------------------------------------
ENV_INT_SEQ SE_INT32 NOT NULL 10
FACILITY_SEQ SE_INT32 NOT NULL 10
ENV_INT_TYPE_SEQ SE_INT32 NOT NULL 10
ENV_INT_FACILITY_PK_ID SE_STRING NOT NULL 21
ENV_INT_START_DATE SE_DATE NULL 0
ENV_INT_END_DATE SE_DATE NULL 0
ENV_INT_NOTES SE_STRING NULL 2000
ENV_INT_LAST_INSPEC_DATE SE_DATE NULL 0
LAST_MERGED_DATE SE_DATE NULL 0
OBJECTID SE_INT32 NOT NULL 10 SDE Set
>sdetable -o describe -t ENV_INT_TYPE
ArcSDE 10.0 for Oracle10g Build 1343 Thu Feb 17 11:45:42 2011
Attribute Administration Utility
-----------------------------------------------------
Table ENV_INT_TYPE:
Column name Attribute type Null? Length,DPs RowID Column?
-------------------------------------------------------------------------------
ENV_INT_TYPE_SEQ SE_INT32 NOT NULL 10
ENV_INT_TYPE_CODE SE_STRING NOT NULL 10
ENV_INT_TYPE_DESC SE_STRING NULL 100
ENV_INT_TYPE_DESC_LONG SE_STRING NULL 2500
DIVISION_SEQ SE_INT32 NULL 10
START_DATE_DESC SE_STRING NULL 50
END_DATE_DESC SE_STRING NULL 50
START_DATE_DESC_LONG SE_STRING NULL 1000
END_DATE_DESC_LONG SE_STRING NULL 1000
PRIMARY_APP_USER_SEQ SE_INT32 NULL 10
ORIGINATING_SYSTEM_SEQ SE_INT32 NULL 10
ENV_INT_START_PAGE_URL SE_STRING NULL 200
ENV_INT_FACILITY_PAGE_URL SE_STRING NULL 1000
ALWAYS_AUTO_DELETE SE_INT16 NULL 1
EPA_EI_TYPE_DESC SE_STRING NULL 100
IS_EXTERNAL_DATA SE_STRING NULL 1
HIDE_GEOCODES SE_STRING NULL 1
HIDE_LEGAL_COORDS SE_STRING NULL 1
ERA_ENV_INT_GROUP_SEQ SE_INT32 NULL 10
OBJECTID SE_INT32 NOT NULL 10 SDE Set
>sdetable -o describe -t ERA_ENV_INT_GROUP
ArcSDE 10.0 for Oracle10g Build 1343 Thu Feb 17 11:45:42 2011
Attribute Administration Utility
-----------------------------------------------------
Table ERA_ENV_INT_GROUP:
Column name Attribute type Null? Length,DPs RowID Column?
-------------------------------------------------------------------------------
ERA_ENV_INT_GROUP_SEQ SE_INT32 NOT NULL 10
ERA_ENV_INT_GROUP_CODE SE_STRING NOT NULL 10
ERA_ENV_INT_GROUP_DESC SE_STRING NOT NULL 100
ERA_ENV_INT_GROUP_LONG_DESC SE_STRING NOT NULL 2500
GROUP_WEB_URL SE_STRING NULL 200
OBJECTID SE_INT32 NOT NULL 10 SDE Set
Any help would be greatly appreciated.