topic Re: Using the same table view with and without a join throws an error when using SelectLayerByAttribute in Python Questions

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

MikeOnzay — Sat, 11 Dec 2021 20:05:01 GMT

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

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

DanPatterson_Retired — Tue, 13 Oct 2015 03:29:38 GMT

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.

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

JoshuaBixby — Tue, 13 Oct 2015 13:48:05 GMT

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.

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

MikeOnzay — Tue, 13 Oct 2015 14:00:07 GMT

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.

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

MikeOnzay — Tue, 13 Oct 2015 14:11:03 GMT

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

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

DanPatterson_Retired — Tue, 13 Oct 2015 14:15:45 GMT

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

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

MikeOnzay — Sat, 11 Dec 2021 20:05:04 GMT

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.

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

JoshuaBixby — Sat, 11 Dec 2021 20:05:07 GMT

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
>>>

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

MikeOnzay — Tue, 13 Oct 2015 17:24:59 GMT

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.

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

JoshuaBixby — Wed, 14 Oct 2015 16:47:55 GMT

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

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

AlexanderNohe1 — Wed, 14 Oct 2015 17:47:11 GMT

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?

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

DanPatterson_Retired — Wed, 14 Oct 2015 18:47:16 GMT

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 (?!).

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

MikeOnzay — Sat, 11 Dec 2021 20:05:10 GMT

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.

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

curtvprice — Thu, 15 Oct 2015 15:18:16 GMT

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.

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

MikeOnzay — Sat, 11 Dec 2021 20:05:13 GMT

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'")

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.

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

curtvprice — Sat, 11 Dec 2021 20:05:15 GMT

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)

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

MikeOnzay — Wed, 04 Nov 2015 21:26:31 GMT

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.

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

JoshuaBixby — Wed, 04 Nov 2015 21:37:16 GMT

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.