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

MikeOnzay · ‎10-12-2015

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

MikeOnzay · ‎10-13-2015

In the first part of my script the view is created with a where clause which I think is like a selection but not something you can really clear. Then I do the join. Then some calculations. Initially I was trying to do a new selection on that view and it was failing even after removing the join. My scenario is a little different from what you are asking although I don't disagree with it. In some earlier versions of my script when I was trying to figure out the correct workflow (I had more selections in the second part) I was intentionally clearing the selections.

JoshuaBixby · ‎10-14-2015

Can you post more complete code? You mention doing some "calculations," what calculations specifically before trying to remove the join.

MikeOnzay · ‎10-14-2015

The script builds a table in a file gdb using other data sources. That table is is joined to the SDE featureclass and then 4 fields in the FC are calculated. In the code below I'm only showing the first of the 4 fields that gets calculated from the join table. The second calculate in row 3 below doesn't use any value from the join table but I need to calculate this value now while the tables are joined.

arcpy.AddJoin_management(in_layer_or_view="missu_lyr", in_field="TicketNo", join_table=path_to_join_table, join_field="TicketNo", join_type="KEEP_COMMON")

with arcpy.da.Editor(wksp) as edit:
        arcpy.CalculateField_management('missu_lyr', sdeFieldPrefix + 'TicketStatus',"!ticketstatus.TicketStatus!",expression_type="PYTHON_9.3",code_block="#")
        arcpy.CalculateField_management('missu_lyr', sdeFieldPrefix + 'OpenClosed','"O"',expression_type="PYTHON_9.3",code_block="#")

arcpy.RemoveJoin_management('missu_lyr')

At this point missu_lyr is still a subset (the subset being that the where_clause when the view was setup had: where_clause = "OpenClosed = 'O' OR OpenClosed IS NULL") of the the entire data without the join now. I just found that if I tried to do a new selection on it

arcpy.SelectLayerByAttribute_management("missu_lyr","NEW_SELECTION","TicketStatus = 'Marked' OR TicketStatus = 'Clear/No conflict'")

the selection failed. It was only after deleting the view and creating a new view did the selection work.

curtvprice · ‎10-15-2015

After you remove the join, check the field list to see if it is messed up.

print([f.name for f in arcpy.ListFields("missu_lyr")])

It does sound like you could have a bug there. If you get an answer from Esri support please add it to the thread.

MikeOnzay · ‎10-15-2015

Before the join the fields look like this:

u'TicketStatus'

During the join all of the fields look like:
u'RockGIS.DPWUTIL.MissUtilityTickets_TEST.TicketStatus'

arcpy.SelectLayerByAttribute_management("missu_lyr","NEW_SELECTION",
    "TicketStatus = 'Marked' OR TicketStatus = 'Clear/No conflict'")

or

arcpy.SelectLayerByAttribute_management("missu_lyr","NEW_SELECTION",(
    "RockGIS.DPWUTIL.MissUtilityTickets_TEST.TicketStatus = 'Marked' OR"
    "RockGIS.DPWUTIL.MissUtilityTickets_TEST.TicketStatus = 'Clear/No conflict'"))

throw ExecuteError: ERROR 000358: Invalid expression

If I remove the join the fields look like this: u'TicketStatus'

The selection still doesn't not work. I have to delete the view and create a new one and then the selection will work.

curtvprice · ‎10-15-2015

I suggest double quotes for around those long, gnarly SDE table name prefixes.

(I'm using \" to embed them below.)

Forgive me editing your posts, but they were being cut off so we couldn't see the code.

arcpy.SelectLayerByAttribute_management("missu_lyr","NEW_SELECTION",(  
    "\"RockGIS.DPWUTIL.MissUtilityTickets_TEST.TicketStatus\" = 'Marked' OR "  
    "\"RockGIS.DPWUTIL.MissUtilityTickets_TEST.TicketStatus\" = 'Clear/No conflict'"))

BTW Python string formatting is your friend with messy expressions like this, also you can use IN to simplify things:

fld = '"RockGIS.DPWUTIL.MissUtilityTickets_TEST.TicketStatus"'
where = "{} IN ('Marked', 'Clear/No conflict')".format(fld)
arcpy.SelectLayerByAttribute_management("missu_lyr", "", where)

MikeOnzay · ‎11-04-2015

After working with ESRI tech support on this for the last few weeks I'm answering my original question / discussion by stating that ESRI agrees that removing the join should have not caused the subsequent use of SelectByLayerAttribute to fail. It is a bug and they were able to reproduce the error I was seeing using their own test data.

BUG-000091905 : If an "OR" operator is specified on a table view and that table view previously participated in a join, running the Select by Attributes geoprocessing tool encounters error 000358: Invalid expression

In working with MY case, ESRI determined that using the IN operator instead of an OR would be a workaround. I can confirm that this works. However, using IN may not work in all cases.

In researching this error ESRI also found a similar bug NIM097634 - The Select Layer By Attribute where_clause tool fa.. that was found in 2013 and is still open.

Hopefully this new bug will help both bugs rise to the top and get fixed soon.

JoshuaBixby · ‎11-04-2015

Mike Onzay, thanks for the follow up, much appreciated. In your case, I am glad there is a workaround, but I always have to shake my head when those 2+ year old bugs that are still open rear their ugly heads.