Joining SDE feature class to SDE table fails in 10.2

[WillAllender]

I originally posted this in Python, but maybe it makes more sense to put it here.  The Python workflow below also does not work if I take each step with ArcToolbox.  Simple workflow:
Grab a feature class and make a Feature Layer
Join a table to the Feature Layer

Is this workflow flawed?

>>> arcpy.MakeFeatureLayer_management(r'Database Connections\GISonGISWT.sde\GIS.V_NM_NIT_BRID_SDO_DT',"BRID_Decodes")
<Result 'BRID_Decodes'>
>>> arcpy.GetCount_management("BRID_Decodes")
<Result '203'>
>>> arcpy.AddJoin_management("BRID_Decodes","IIT_NE_ID",r'Database Connections\GISonGISWT.sde\GIS.XLAR_MV_RIL_BRID_DECODE',"IIT_NE_ID","KEEP_ALL")
<Result 'BRID_Decodes'>
>>> arcpy.GetCount_management("BRID_Decodes")
<Result '-1'>

That second GetCount should also result with 203 records, not -1.

Other rambling notes:
1) This workflow was built in ArcGIS Desktop 10.0 against a 9.3.1 database and works perfectly there. It fails in Desktop 10.1 with the same 9.3.1 database.
2) This workflow works fine using Desktop 10.2 on a 9.3.1 database.
3) This workflow fails if the database is at ArcGIS 10.2.
4) The join fields are both Long Integer.
5) The database is Oracle 11g on Sun OS.
6) If I move the join table to a file or personal GDB, the workflow works fine, even in 10.2. However, the Esri documentation states that the join table can be an SDE table, and I would prefer to not be copying a bunch of lookup tables to a local file. I need the above workflow to work on dozens of feature classes and tables.

Thanks in advance for any insight you have on this.

Cheers!
Will

[WillAllender]

Does anyone out there have an SDE 10.2 Oracle11g setup and is willing to have a go at this?  I have attached a 3-record personal gdb with mocked up data.  3 features and 3 tabular records.  The join field is JOINFIELD.

This is fresh, mocked up, completely unreal data.  If I load it into a 9.3.1 SDE instance, the AddJoin function works.  If I load it into a 10.2 database, the AddJoin fails.  It's that simple.

For testing, I am copying/pasting commands into the Python window in ArcCatalog:
>>> arcpy.MakeFeatureLayer_management(r'<some_sde_connection.sde>\GIS.TESTFC',"TESTLAYER")
>>> arcpy.GetCount_management("TESTLAYER")
>>> arcpy.AddJoin_management("TESTLAYER","JOINFIELD",r'<some_sde_connection.sde>\GIS.TESTTABLE',"JOINFIELD")
>>> arcpy.GetCount_management("TESTLAYER")

My ultimate goal is to export newly joined data out to a data warehouse.  The record count of -1 from ArcGIS 10.2 is producing zero records.

Thanks,
Will

[VinceAngelo]

These sort of issues are best addressed by opening an incident with Tech Support.  They always
have instances configured for testing data.

- V

[WillAllender]

Thank you for the reply, Vince.

I opened an incident 8 days ago with esri Tech Support and stumped the first technician during his initial screen share session.  A second tech said, just today, that she will try to get to me, to do another screen share, maybe tomorrow, or next Monday.  I was a little surprised by this, considering we are on an ELA.

Anyway, I thought I would throw it out there to the user community, since doing Joins is about as common as doing an Identify.

[VinceAngelo]

I've never used a join -- a view or other multi-table query has always sufficed.
I doubt joins are even a hundredth as frequent as identify operations.

Still, it should work, but I doubt the regression tests failed, so it's now a matter
of figuring out why this particular use case has issues.

Tech Support analysts always have a number of irons in the fire, and sometimes
it takes a little while to get access to specialist resources that can help resolve
vexing issues.

- V

[AsrujitSengupta]

Will,

I tried the whole process using ArcMap tools and it works as expected.

sde 10.2 (oracle 11.2.0.1)
Desktop 10.2

My Workflow:
1. Copy the TESTFC and TESTTABLE into the sde 10.2 oracle gdb
2. Register the data as versioned
3. Add both the fc and table to ArcMap.
4. Use the "Make Feature Layer" tool for the testfc layer --> resulting layer is testfc_layer
5. Get Count--> 3
6. Use "Add Join" management tool and create a join b/w testfc_layer and testable based on the 'JOINFIELD'--> Joins successfully
7. Get Count--> 3

For testing sake, I also tested the same tools in ArcCatalog via Model Builder and Script (have attached my script and model)

Let me know if you were mentioning the same steps or I'm missing something.

Regards,

[WillAllender]

asrujit,

Thank you for your assistance!  The one difference that I see in your workflow vs. mine is that you are doing it in ArcMap, after the layer and table have been added to the ArcMap session.  When I do it that way, it works for me too.

For my workflow, I test in the ArcCatalog Python window, without an ArcMap session open.  Or I test in PyScripter.  Ultimately, I need to do unattended nightly batch processing, without user interaction.

Again, thank you for trying.  Since you already have the data in SDE 10.2, I would be in your debt if you could try the same steps in ArcCatalog.

Thank you,
Will

[WillAllender]

Asrujit,

Thank you for your assistance!  That is the same workflow I am using.

Again, thank you for trying!
Will

[WillAllender]

Adding more information to the topic

Desktop 10.2
SDE 10.2, 64-bit Oracle 11.2.0.3
Using Asrujit's example of building the workflow in ArcMap, I did the following:
1) added the feature class and table to ArcMap
2) used MakeFeatureLayer from ArcToolbox
3) used AddJoin from ArcToolbox
4) Ran GetCount, which resulted in -1
5) opened the table from the Table of Contents and visually saw all of my records, with a correct join performed
6) attempted to export data to a new feature class
7) finally got a useful error message:
"Error exporting data. Underlying DBMS error [ORA-25156: old style outer join (+) cannot be used with ANSI joins] [DBJoin1]"
😎 Got my DBA involved to capture the error when I ran it again
9) ArcMap is generating the following SQL, which fails (SDE 10.2):
[INDENT]SELECT GIS.TESTFC.OBJECTID,
       GIS.TESTFC.JOINFIELD,
       GIS.TESTFC.SHAPE,
       GIS.TESTTABLE.OBJECTID,
       GIS.TESTTABLE.JOINFIELD,
       GIS.TESTTABLE.ADDITIONALATTRIBUTES,
       SHAPE.fid,
       SHAPE.numofpts,
       SHAPE.entity,
       SHAPE.points,
       SHAPE.rowid
  FROM GIS.TESTFC
  LEFT OUTER JOIN GIS.TESTTABLE
    ON GIS.TESTFC.JOINFIELD = GIS.TESTTABLE.JOINFIELD, GIS.F1888 SHAPE
where SHAPE.FID(+) = GIS.TESTFC.SHAPE;[/INDENT]
10) ran through all the same steps using data from SDE 9.3.1
11) ArcMap generates the following SQL, which succeeds (SDE 9.3.1, Oracle 10g):
[INDENT]SELECT OBJECTID,
       JOINFIELD,
       SHAPE,
       SHAPE.LEN,
       SHAPE.fid,
       SHAPE.numofpts,
       SHAPE.entity,
       SHAPE.points,
       SHAPE.rowid
  FROM GIS.TESTFC, GIS.F3501 SHAPE
where SHAPE.FID(+) = GIS.TESTFC.SHAPE;


SELECT JOINFIELD, OBJECTID, ADDITIONALATTRIBUTES
  FROM GIS.TESTTABLE
WHERE (JOINFIELD in (444444, 555555, 666666));[/INDENT]

So, as Vince pointed out, we are a particular use case and it would appear that there is something about our implementation/environment that is causing ArcMap to generate bogus SQL (but Asrujit's works just fine).

I bundled up all my notes and sent them off to tech support.  Just wanted to share in case someone else came across this issue.

[VinceAngelo · Accepted solution]

It appears that you're still using SDEBINARY/SDELOB.  What happens if you copy
the layer into ST_GEOMETRY storage?

- V