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

2920
17
Jump to solution
10-12-2015 08:06 PM
MikeOnzay
Regular Contributor

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

0 Kudos
1 Solution

Accepted Solutions
MikeOnzay
Regular Contributor

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.

View solution in original post

17 Replies
DanPatterson_Retired
MVP Esteemed Contributor

I am curious whether you actually kill the link to the previous view in your script by deleting references to it at the end of your first process?  It appears that it is floating around in some state after the first, which causes issues as you move forward.  I have experienced similar pecularities with 'things' in Arcmap if a script tool fails between successive runs since remnants remain in memory.  I find it useful to maintain a commented line in my scripts that contains the locals().keys() of variables that I create so I can delete them fully at the completion of a script or to be used uncommented should a script fail.

MikeOnzay
Regular Contributor

All I know how to do is use

arcpy.Delete_management('missu_lyr')

to delete that view. I'm not aware that I can do anything else. Your suggestion of keeping track of locals().keys() is something new for me that I will explore.

I think my approach of creating a new view to do selections is reasonable. I'm not processing large files so I'm not worried about running out of memory before the script ends

0 Kudos
DanPatterson_Retired
MVP Esteemed Contributor

let's assume that you have a variable in your script that references an object

my_variable = "missu_lyr"  # where missu_lyr is a layer in ArcMap

...blah do stuff

...do more

... done

arcpy.Delete_management('missu_lyr')

del my_variable   # Add this line... just a shot just in case the variable is still referencing the layer

0 Kudos
MikeOnzay
Regular Contributor

Are you saying that rather than just using arcpy.MakeTableview by itself it would be better to call it while it is being assigned to a variable?

I did a test.

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

arcpy.GetCount_management("missu_lyr")
<Result '27'>
arcpy.Delete_management('missu_lyr', 'GPTableView')
<Result 'true'>

myvar
<Result 'missu_lyr'>

# I did not expect to see this

arcpy.GetCount_management("missu_lyr")

Traceback (most recent call last):
  File "<interactive input>", line 1, in <module>
  File "C:\Program Files (x86)\ArcGIS\Desktop10.3\ArcPy\arcpy\management.py", line 15306, in GetCount
    raise e
ExecuteError: Failed to execute. Parameters are not valid.
ERROR 000732: Input Rows: Dataset missu_lyr does not exist or is not supported
Failed to execute (GetCount).

I did it the other way too.

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

arcpy.GetCount_management("missu_lyr")
<Result '27'>

del myvar
arcpy.GetCount_management("missu_lyr")
<Result '27'>

Maybe this is a case where the variable is just referencing the object and not actually holding it? Deleting the variable just deletes the reference.

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

My response here is mostly an aside to the original dialogue, but there are a couple of things I think are worth bringing up.

First, it is helpful to format your code or use syntax highlighting.  There are numerous blog posts and comments about how to do it.  A good place to start is Curtis Price​'s blog post, Posting Code blocks in the new GeoNet​.

Second, most, if not all, ArcGIS geoprocessing tools return a Result object.  The interactive Python window hints at that fact by showing <Result '27'> in the window when you run the Get Count tool in your test.  Looking at an example might be helpful:

>>> tmpTable = arcpy.CreateTable_management('in_memory','temp')
>>> type(tmpTable)
<class 'arcpy.arcobjects.arcobjects.Result'>
>>> tmpTable.__repr__()
"<Result 'in_memory\\\\temp'>"
>>> tmpTable.__str__()
'in_memory\\temp'
>>>

As you can see with line #2-3, the Create Table tool returns a Result object.  The Result object's representation, via the representation dunder method (object.__repr__), is what users are used to seeing in the interactive Python window (line #5).  Whereas the __repr__ method is the “official” string representation of an object, the __str__ method (object.__str__) is the “informal” string representation of an object, which is what geoprocessing tools are using to determine the path to objects and what not when a Result object from one tool is passed to another tool.

I mention the above because using the built-in Python delete function won't delete the object being referenced by the Result object.  To delete ArcPy/ArcGIS objects, you should use the Delete tool first and then you can delete the variable referencing the result object if you want.

>>> arcpy.Delete_management(tmpTable)
<Result 'true'>
>>> tmpTable
<Result 'in_memory\\temp'>
>>> del tmpTable
>>>
MikeOnzay
Regular Contributor

Thanks for the link on how to post code because I know that I'm supposed to do that but it was not obvious to me last night as to where I could find it on the editor toolbar.

0 Kudos
AlexanderNohe1
Regular Contributor II

Read this post briefly and think this knowledge base article may be of some use:

44214 - Why is the del statement in Python unable to delete data referenced by variables?

DanPatterson_Retired
MVP Esteemed Contributor

Alexander even though del obviously can't be used to delete data (I am not sure that was implied anywhere) but I also assuming del wouldn't remove arcmap locks on data either, but lets just lets python go on its way leaving arcmap oblivious to it (?!). 

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

Are you removing or clearing the selection before removing the join?  If you make a selection against a Feature Layer or Table View and don't clear it before breaking a join, it seems to hang onto references of the join after you try to remove it.

0 Kudos