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.RemoveJoin_management('missu_lyr') 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