Table join on SDE FC in Python?

[JeremyRead]

I am simply trying to join a table from an SDE featureclass to another table, then calculate some field values after the join.  It is a very simple process, and relatively easy to do in ArcMap itself, but I'm having a really hard time getting it to work in Python.  Apparently AddJoin_management will not work for SDE FCs, so what options are there?

# Import arcpy module
import arcpy

# Local variables:
AUSTIN_WWManhole = "Database Connections\\wwwgisoraold.sde\\AUSTIN.AustinWastewater\\AUSTIN.WWManhole"
IMSV7_COMPSMH = "Database Connections\\Hansen_Tables_Production.odc\\IMSV7.COMPSMH"

# Join tables based on COMPKEY field, only keep matching records
arcpy.AddJoin_management(AUSTIN_WWManhole, "COMPKEY", IMSV7_COMPSMH, "COMPKEY", "KEEP_COMMON")

# Select records where METERED = 'Y'
arcpy.SelectLayerByAttribute_management(AUSTIN_WWManhole, "NEW_SELECTION", "\"IMSV7.METERED\" = 'Y'")

# Calculate METERINDICATOR = "Y" for selected records
arcpy.CalculateField_management(AUSTIN_WWManhole, "AUSTIN.WWManhole.METERINDICATOR", "\"Y\"", "PYTHON_9.3", "")

# Select records where METERED = 'N' and METERINDICATOR = 'Y'
arcpy.SelectLayerByAttribute_management(AUSTIN_WWManhole, "NEW_SELECTION", "\"IMSV7.METERED\" = 'N' AND AUSTIN.WWManhole.METERINDICATOR = 'Y'")

# Calculate METERINDICATOR = "N" for selected records
arcpy.CalculateField_management(AUSTIN_WWManhole, "AUSTIN.WWManhole.METERINDICATOR", "\"N\"", "PYTHON_9.3", "")

print "Complete"

[MathewCoyle]

I usually create a local copy and use arcpy.JoinField_management()

[JeremyLuymes]

I think what you have there will work, you just need to convert your feature classes to feature layers.

Use arcpy.MakeFeatureLayer(AUSTIN_WWMHOLE,"Manhole") for example, and then just use "Manhole" instead of your feature class for inputs in the other commands.

For your table you might have to use arcpy.MakeTableView to get the join to work as well.

EDIT: The joinfield mentioned above probably won't work because it permanently adds a field...I assume you wouldn't want that for an SDE feature class.

[JeremyRead]

I think what you have there will work, you just need to convert your feature classes to feature layers.

Use arcpy.MakeFeatureLayer(AUSTIN_WWMHOLE,"Manhole") for example, and then just use "Manhole" instead of your feature class for inputs in the other commands.

For your table you might have to use arcpy.MakeTableView to get the join to work as well.

EDIT: The joinfield mentioned above probably won't work because it permanently adds a field...I assume you wouldn't want that for an SDE feature class.

Thanks, I will try that.  And you're right, I don't want to permanently add a field... I just want to join them to do the field calculations.

[JeremyRead]

Well, I successfully used arcpy.MakeFeatureLayer_management to create the "Manhole" layer, however when I add it as a parameter in arcpy.AddJoin_management it says that "Manhole is not defined."

Also tried making a table view out of WWManhole and still got the same error:  "Manhole is not defined".

Don't understand why it is saying this when I just created it right before running the AddJoin command....

[JeremyLuymes]

Are you using quotations around the feature layers/tables. Something like this:

# Import arcpy module
import arcpy

# Local variables:
AUSTIN_WWManhole = "Database Connections\\wwwgisoraold.sde\\AUSTIN.AustinWastewater\\AUSTIN.WWManhole"
IMSV7_COMPSMH = "Database Connections\\Hansen_Tables_Production.odc\\IMSV7.COMPSMH"

arcpy.MakeFeatureLayer(AUSTIN_WWManhole,"Manhole")
arcpy.MakeTableView(IMSV7_COMPSMH,"Table")

# Join tables based on COMPKEY field, only keep matching records
arcpy.AddJoin_management("Manhole", "COMPKEY", "Table", "COMPKEY", "KEEP_COMMON")

# Select records where METERED = 'Y'
arcpy.SelectLayerByAttribute_management("Manhole", "NEW_SELECTION", "\"IMSV7.METERED\" = 'Y'")

# Calculate METERINDICATOR = "Y" for selected records
arcpy.CalculateField_management("Manhole", "AUSTIN.WWManhole.METERINDICATOR", "\"Y\"", "PYTHON_9.3", "")

# Select records where METERED = 'N' and METERINDICATOR = 'Y'
arcpy.SelectLayerByAttribute_management("Manhole", "NEW_SELECTION", "\"IMSV7.METERED\" = 'N' AND AUSTIN.WWManhole.METERINDICATOR = 'Y'")

# Calculate METERINDICATOR = "N" for selected records
arcpy.CalculateField_management("Manhole", "AUSTIN.WWManhole.METERINDICATOR", "\"N\"", "PYTHON_9.3", "")

print "Complete"

[JeremyRead]

Nevermind, finally got it to work after a lot of trial and error:

# Import arcpy module
import arcpy

# Local variables:
IMSV7_COMPSMH = "C:\\Users\\readj\\AppData\\Roaming\\ESRI\\Desktop10.0\\ArcCatalog\\Hansen_Tables_Production.odc\\IMSV7.COMPSMH"
AUSTIN_WWManhole = "Database Connections\\wwwgisoraold.sde\\AUSTIN.AustinWastewater\\AUSTIN.WWManhole"
WWManhole_View = "WWManhole_View"

print " "
print "Update Meter Indicator Script STARTED: " + str(datetime.datetime.now())
print " "

# Make Table View of WWManhole
arcpy.MakeTableView_management(AUSTIN_WWManhole, WWManhole_View)

# Join tables based on COMPKEY field, only keep matching records
arcpy.AddJoin_management(WWManhole_View, "COMPKEY", IMSV7_COMPSMH, "COMPKEY", "KEEP_COMMON")

print "Tables Joined: " + str(datetime.datetime.now())

# Select records where METERED = 'Y'
arcpy.SelectLayerByAttribute_management(WWManhole_View, "NEW_SELECTION", "METERED = 'Y'")

# Calculate METERINDICATOR = "Y" for selected records
arcpy.CalculateField_management(WWManhole_View, "AUSTIN.WWManhole.METERINDICATOR", "'Y'", "PYTHON")

print "Calculated METERINDICATOR = 'Y': " + str(datetime.datetime.now())

# Select records where METERED = 'N' and METERINDICATOR = 'Y'
arcpy.SelectLayerByAttribute_management(WWManhole_View, "NEW_SELECTION", "METERED = 'N' AND AUSTIN.WWManhole.METERINDICATOR = 'Y'")

# Calculate METERINDICATOR = "N" for selected records
arcpy.CalculateField_management(WWManhole_View, "AUSTIN.WWManhole.METERINDICATOR", "'N'", "PYTHON")

print "Calculated METERINDICATOR = 'N': " + str(datetime.datetime.now())

print " "
print "Update Meter Indicator Script COMPLETED: " + str(datetime.datetime.now())