AnsweredAssumed Answered

Using the same table view with and without a join throws an error when using SelectLayerByAttribute

Question asked by monzay on Oct 12, 2015
Latest reply on Nov 4, 2015 by bixb0012

I've been working on a python script for awhile now and I've been stumped for over a week as to why the script would throw an error when I tried to use SelectLayerByAttribute after I removed a join on a tableview that is based on a SDE featureclass.


I finally figured out the solution today but I don't really understand why it works. Or, I should say, I don't understand why it didn't work before.  The solution was to break my script into two parts: Using one view for the AddJoin and then deleting that view and creating a separate view to perform an action without the join.


I had a working script that created a tableview using a where clause from an SDE featureclass, pulled in data from other sources, joined that data to the tableview, did some calculations, then removed the join and deleted the tableview.


arcpy.MakeTableView_management(os.path.join(wksp, targetFC),"missu_lyr",where_clause = "OpenClosed = 'O' OR OpenClosed IS NULL", workspace="")


Later on I needed to do an additional calculation on the featureclass but I didn't need the table joined anymore. I tried to just continue my workflow by removing that join and using the SelectLayerByAttribute command to get a subset and then calculate the values. However, I kept getting an "ERROR 000358: Invalid expression" on it.


arcpy.AddJoin_management(in_layer_or_view="missu_lyr", in_field="TicketNo", join_table=path_to_table, join_field="TicketNo", join_type="KEEP_COMMON")
# do stuff
arcpy.SelectLayerByAttribute_management("missu_lyr","NEW_SELECTION","TicketStatus = 'Marked' ")
Executing: SelectLayerByAttribute missu_lyr NEW_SELECTION " "TicketStatus" =  'Marked'"
Start Time: Mon Oct 12 17:30:07 2015
ERROR 000358: Invalid expression
An invalid SQL statement was used.
Failed to execute (SelectLayerByAttribute).

What really stumped me is that I could run my script to a certain point (I'm using PyScripter) and then try the select command and check the results with GetCount_management and I get what expected. I also did the workflow in the interactive window in ArcMap with no errors.


After 37 minutes on the phone with ESRI tech support today I could not give up (they were elevating my issue) and I finally tried removing the join, deleting the view and recreating a new view with a new name and the same syntax.


arcpy.MakeTableView_management(os.path.join(wksp, targetFC),"missu_lyr2",where_clause = "OpenClosed = 'O' OR OpenClosed IS NULL", workspace="")
arcpy.SelectLayerByAttribute_management("missu_lyr2","NEW_SELECTION","TicketStatus = 'Marked' ")

Then the selection worked. The calculation worked. My script finally worked.


Does the act of creating a join and then removing it leave some funny prefixed fieldname behind? Or is it a best practice to always create a new view after a join before doing selections?


Message was edited by: Mike Onzay Edited to add syntax highlighting