Various issues with SelectLayerByAttribute_management within Joined Features in Python

[DerekNelson]

The short version is this:

I have hundreds of original GDBs (backup copies) and the post-processed versions of same GDBs. The feature datasets, feature classes and attribute names are identical except for 5 additional fields in the originals. During the post processing those fields were eliminated during Append processes because they weren't to standard...no problem. My solution was to create feature layers, join based off attribute field, select where field IS NULL and field calculate values over. 

Problem: I received errors specific to arcpy.SelectLayerByAttribute() that suggest "Invalid Expression". I searched and tested recommendations, which sometimes are exact opposites of other recommendations (and folks claim it worked) but settled on the fact that it isn't truly an expression error (same syntax expressions work fine when applied to non-joined features). I decided to test manually to see what would happen and some wonky things occur where fields automatically assume properties of others when viewing tables in ArcMap, other fields from joined table don't show and I assume because of the similarity of datasets, features and attributes things are getting crossed in_memory??? I have tried a number of different things to no avail...print statements in there for my own sanity and the split tool called on because I thought same feature class name might be cause but still nada...it has to be something with joined attribute field name is my guess and yes I have tried wherecd = '"' + tFC + '_tLYR.createDate" IS NULL' among other recommendations.

Does anybody see anything off the top that would cause this error? Any help appreciated...

[DerekNelson]

After more testing it definitely seems to be an issue of identical field names across two separate features and an incapability of ArcGIS somewhere to reconcile which is which...ONLY running into it through Python. In effect I think adding the feature or table name prefix does nothing (i.e. featureclassname.fieldname) in regards to this particular geoprocessing tool in Python. A little frustrating especially when Add Join geoprocessing tool page shows almost an identical workflow for Python at bottom but doesn't warn about naming issues...

Any ideas? Or am I left to rename 10,000+ attribute names for my target GDB datsets so that script can run and then re-Append them or rename them once again back to correct field names for final product?

Tell me I am wrong please...

[JoshuaBixby]

Can you print out one of your wherecd statements for us to see?

Also, are you specifying the table or layer name when building your statements?  It appears you are using layer names, but I want to ask in case I am misreading your code.

[DerekNelson]

so the print statements look fine and end up being "featname_tLYR.createDate" IS NULL...well fine in the sense that similar statements without joined table work perfectly

[RandyBurton]

Can you print the field names of your joined feature? I suspect they might be different than what you are expecting.

[DerekNelson]

You are on to something Randy..what am I doing wrong? something with the field_names call? it printed out Shape_Area about 30 times...I am assuming it sees all my fields as Shape_Area...is there a fix?

[DerekNelson]

nevermind...that isn't it...I had screwed up in my trial version on the side..when I ran exact same snippet that's in original on joined it printed out the way I hoped..with all field names...

oh wait...you are on it doggone it...so field names printed out correctly this time but it doesn't use the layer name it uses original feature/table name then ".fieldname"...

so a combination of what you and Joshua bring to light...I would assume though that if I went back and never changed one of the feature classes and left it exact same name I would still run into issues right? will it be able to distinguish between the 2? I will test in meantime.

[JoshuaBixby · Accepted solution]

SQL statements don't operate on layers, they operate on the data sets behind the layers, which is why certain SQL statements will fail if the data source behind a layer changes types, e.g., re-sourcing a layer to a shape file from a file geodatabase feature class.  The driver that is handling the SQL statement has no idea about layer or table view names.  Commonly, people don't run into this issue because the layer names are the same as the table or feature class names.  In this case, they are different.

[DerekNelson]

still run into issues when you are comparing different GDBs with same datasets, feature classes, and attribute names it seems but I can write quick script to append some string to end of all feature class names in target GDB so that I can then run joins and select layers and calculate fields while looping through the other hundreds of GDBs..

not a 100% fix because of that one issue but it's a workaround and I am smarter on it now..thanks fellas!

[RandyBurton]

Check out example 2 in this blog: Turbo Charging Data Manipulation with Python Cursors and Dictionaries.  It describes a process that could help since there are issues with doing a join for your project..