topic Re: Joining SDE feature class to SDE table fails in 10.2 in Data Management Questions

Joining SDE feature class to SDE table fails in 10.2

WillAllender — Thu, 21 Nov 2013 16:35:31 GMT

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

Re: Joining SDE feature class to SDE table fails in 10.2

WillAllender — Thu, 21 Nov 2013 20:02:20 GMT

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

Re: Joining SDE feature class to SDE table fails in 10.2

VinceAngelo — Thu, 21 Nov 2013 21:50:26 GMT

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

- V

Re: Joining SDE feature class to SDE table fails in 10.2

WillAllender — Thu, 21 Nov 2013 22:41:08 GMT

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.

Re: Joining SDE feature class to SDE table fails in 10.2

VinceAngelo — Fri, 22 Nov 2013 00:16:52 GMT

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

Re: Joining SDE feature class to SDE table fails in 10.2

AsrujitSengupta — Fri, 22 Nov 2013 00:22:34 GMT

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,

Re: Joining SDE feature class to SDE table fails in 10.2

WillAllender — Fri, 22 Nov 2013 10:00:41 GMT

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

Re: Joining SDE feature class to SDE table fails in 10.2

WillAllender — Fri, 22 Nov 2013 10:08:00 GMT

Asrujit,

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

Again, thank you for trying!
Will

Re: Joining SDE feature class to SDE table fails in 10.2

WillAllender — Fri, 22 Nov 2013 20:28:05 GMT

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.

Re: Joining SDE feature class to SDE table fails in 10.2

VinceAngelo — Fri, 22 Nov 2013 23:14:08 GMT

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

- V

Re: Joining SDE feature class to SDE table fails in 10.2

WillAllender — Sat, 23 Nov 2013 01:10:11 GMT

Son of a b... that actually worked. Converting to ST_GEOMETRY. I would love to know how you arrived at that suggestion.

It doesn't help me with my ultimate/root problem, but it does somewhat help explain what's going on. Root problem is that I am data mining someone else's SDE and they store in SDO, which I have no control or influence over. So my test data is now invalid, but that is another issue.

Anyway, THANKS!

Re: Joining SDE feature class to SDE table fails in 10.2

AsrujitSengupta — Sat, 23 Nov 2013 01:30:11 GMT

Adding more to what Vince already mentioned, you were actually hitting a known Bug:

NIM096131: Joining a table or a feature class through a field from another joined feature class or table returns ORA-25156 when the geometry storage SDELOB is used.

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

Glad you found the workaround!! Enjoy the weekend ahead!

Re: Joining SDE feature class to SDE table fails in 10.2

VinceAngelo — Sat, 23 Nov 2013 02:34:12 GMT

You provided the answer in the SQL. The join to the Fn table has the wrong syntax;
ST_GEOMETRY has no need to do that join.

- V

Re: Joining SDE feature class to SDE table fails in 10.2

MichaelVolz — Mon, 25 Nov 2013 15:25:02 GMT

Vince:

Is ESRI by any chance starting to migrate away from SDELOB geometry storage where only ST_GEOMETRY will be supported some time in the future?

Re: Joining SDE feature class to SDE table fails in 10.2

VinceAngelo — Mon, 25 Nov 2013 16:09:37 GMT

They seem to have at 10.2 for PostgreSQL. I'm not anywhere near the decision chain
to report the future, but there's strong benefits to not having a 3-table architecture,
so it's probably not a bad idea to plan for such a future.

- V