Join doesn't work - Invalid Identifier

[DahaeHwang]

I posted this in ArcGIS for Desktop forum too, but think I might get some insight from SDE forum too. I will delete the other one later.

When I try to make a join (a feature class with standalone table), I get Invalid Identifier error as below.
One or more layers failed to draw:
schemaName.featureClassName: Underlying DBMS error [ORA-00904: "V__22"."joinedFieldName": invalid identifier
I could successfully validate the join, but it happens when it draws the joined layer.
I am using ArcMap 10.1, with ArcSDE 10.1 (SP1) in Oracle 11.2.0.1. The feature class(polygon layer) is registered as versioned and the table is registered with Geodatabase.
Here is the entire error message.
I used 'PARCEL.GPIN' and 'CAMATEST.MAPBLOLOT' to join the layer and table, but in the error message you can see it is creating the view (V__22) by selecting the other column (=TYPE_CODE) in the feature class, not the 'GPIN' that I chose to join...
BTW after join, I don't have a problem in seeing the attribute tables which are joined with the correct fields.


VECTOR.Parcels: Underlying DBMS error [ORA-00904: "V__22"."GPIN": invalid identifier
::SELECT V__22.st_SHAPE$, V__22.TYPE_CODE, V__22.OBJECTID, V__22.st_points,V__22.st_numpts,V__22.st_entity,V__22.st_minx,V__22.st_miny,V__22.st_maxx,V__22.st_maxy,V__22.st_minz,V__22.st_maxz,V__22.st_minm,V__22.st_maxm,V__22.st_area$,V__22.st_len$,V__22.st_rowid FROM (SELECT 1 st_SHAPE$,b.TYPE_CODE ,b.OBJECTID,b.SHAPE.points as st_points,b.SHAPE.numpts as st_numpts,b.SHAPE.entity as st_entity,b.SHAPE.minx as st_minx,b.SHAPE.miny as st_miny,b.SHAPE.maxx as st_maxx,b.SHAPE.maxy as st_maxy,b.SHAPE.minz as st_minz,b.SHAPE.maxz as st_maxz,b.SHAPE.minm as st_minm,b.SHAPE.maxm as st_maxm,b.SHAPE.area as st_area$,b.SHAPE.len as st_len$,b.rowid as st_rowid FROM VECTOR.Parcels b WHERE SDE.ST_EnvIntersects(b.SHAPE,:1,:2,:3,:4) = 1 AND b.OBJECTID NOT IN (SELECT /*+ HASH_AJ */ SDE_DELETES_ROW_ID FROM VECTOR.D22 WHERE DELETED_AT IN (SELECT l.lineage_id FROM SDE.state_lineages l WHERE l.lineage_name = :lineage_name1 AND l.lineage_id <= :state_id1) AND SDE_STATE_ID = 0) UNION ALL SELECT 2 st_SHAPE$,a.TYPE_CODE ,a.OBJECTID,a.SHAPE.points as st_points,a.SHAPE.numpts as st_numpts,a.SHAPE.entity as st_entity,a.SHAPE.minx as st_minx,a.SHAPE.miny as st_miny,a.SHAPE.maxx as st_maxx,a.SHAPE.maxy as st_maxy,a.SHAPE.minz as st_minz,a.SHAPE.maxz as st_maxz,a.SHAPE.minm as st_minm,a.SHAPE.maxm as st_maxm,a.SHAPE.area as st_area$,a.SHAPE.len as st_len$,a.rowid as st_rowid FROM VECTOR.A22 a,SDE.state_lineages SL WHERE SDE.ST_EnvIntersects(a.SHAPE,:5,:6,:7,:8) = 1 AND (a.OBJECTID, a.SDE_STATE_ID) NOT IN (SELECT /*+ HASH_AJ */ SDE_DELETES_ROW_ID, SDE_STATE_ID FROM VECTOR.D22 WHERE DELETED_AT IN (SELECT l.lineage_id FROM SDE.state_lineages l WHERE l.lineage_name = :lineage_name2 AND l.lineage_id <= :state_id2) AND SDE_STATE_ID > 0) AND a.SDE_STATE_ID = SL.lineage_id AND SL.lineage_name = :lineage_name3 AND SL.lineage_id <= :state_id3) V__22 LEFT OUTER JOIN VECTOR.CAMATEST ON V__22.GPIN = VECTOR.CAMATEST.MAPBLOLOT] [DBJoin1][/COLOR

[AsrujitSengupta · Accepted solution]

Dahae,

You can have a look at the below link. You might be hitting a known Bug:

[ NIM082229 - In ArcGIS 10.1 for Desktop, when an ArcSDE versioned layer is displayed after being joined to an ArcSDE table in Oracle, the following error is returned: "ORA-00904: invalid identifier"]

http://support.esri.com/en/bugs/nimbus/TklNMDgyMjI5

Regards,

[DahaeHwang]

Thanks for the information.

Now I am curious if ESRI will include the fix for this bug in a new patch/service pack. Does anyone know..?
Thank you,

[AsrujitSengupta]

The Bug seems to be fixed in the upcoming release 10.2, as mentioned in the link.

[MichaelVolz]

I am getting the same error message in 10.5.1 through an ArcGIS Enterprise mapservice, but the configuration of GIS data is not the same.  Are there any other reasons why this error message would occur as the AGS logging does not provide enough information for me to figure out the problem?