topic Where Clause is being ignored in MakeTableView in Python Questions

Where Clause is being ignored in MakeTableView

AllenDailey1 — Wed, 11 Oct 2023 00:23:51 GMT

In a standalone script, I am trying to use arcpy.MakeTableView_management(). I'm including a "where clause" in the parameters in order to include only certain records in the resulting Table View. With the ArcMap arcpy runtime, it worked, but with Pro, the "where clause" gets ignored - it has no effect. It doesn't throw an error; the script just runs into trouble later on because it is trying to work with records that it's not designed to work with.

I have tried a variety of different where clauses that target a variety of fields in the source table. None have an effect. I tried manually doing Select by Attributes in Pro (rather than in the script), and that worked fine. I even copied the SQL from the Pro Select by Attributes GUI into my script, but still it had no effect.

I can probably come up with a workaround, but this function should work! Or at least I'd like to know why it isn't working.

The input table is a SQL Server database table, not from any kind of geodatabase.

Here are the relevant lines: (of course my script uses the real names, and bus_lic_tv is used later in the script)

import arcpy arcpy.env.workspace = r"C:\Path\to\Geodatabase.gdb" table_data = r"C:\Users\adailey\AppData\Roaming\Esri\ArcGISPro\Favorites\DatabaseName.sde\Database.dbo.TableName" bus_lic_tv = arcpy.MakeTableView_management(table_data, "bus_lic_no_null_apns", where_clause='APN IS NOT NULL')

Does anyone know why the "where clause" is being ignored?? Thank you!

Re: Where Clause is being ignored in MakeTableView

ChrisUnderwood — Wed, 11 Oct 2023 09:50:03 GMT

Hello @AllenDailey1 , I think your Make Table View statement should look like this :

bus_lic_tv = arcpy.MakeTableView_management(table_data, "bus_lic_no_null_apns","APN IS NOT NULL")

- without the where_clause= part

- use double quotes "" around the where clause.

It works for me with that syntax.

Re: Where Clause is being ignored in MakeTableView

AllenDailey1 — Wed, 11 Oct 2023 22:18:46 GMT

Thank you very much for your suggestion!

I tried your suggestions, and sadly, the "where clause" is still being ignored. So weird.

Re: Where Clause is being ignored in MakeTableView

ChrisUnderwood — Thu, 12 Oct 2023 12:40:47 GMT

Where and how are you checking your bus_lic_no_null_apns View, to see that the "where clause" is being ignored ?

Re: Where Clause is being ignored in MakeTableView

AllenDailey1 — Thu, 12 Oct 2023 15:34:12 GMT

Later in the script, data from the table view is manipulated - hyphens are inserted between the numbers in the field "APN". This throws an error, because that action can't be done on Nulls. So that means the records where APN is null were mistakenly included in the table view (meaning the Where Clause was ignored). For troubleshooting, I also included a print statement that prints out the APN's of the records included in the table view - print("APN is " + APN). For the records that have a value in the APN field, it prints, but for other records it errors out, saying it can't concatenate string and NoneType.

For troubleshooting, I tried running MakeTableView, including a Where Clause, on a geodatabase feature class rather than a SQL Server database table (which was the original data format I was running it on), and the Where Clause worked! So now my theory is that the problem is the data table type. Maybe I will try adding code to my script to first convert the database table into a geodatabase table or something, before doing MakeTableView. That would defeat the purpose of having intermediate products just be in memory, but that could be okay.

Re: Where Clause is being ignored in MakeTableView

AllenDailey1 — Tue, 17 Oct 2023 23:19:12 GMT

I came up with a workaround. Instead of using a "where clause," I just added an "if" statement later in the script to exclude records that would have been excluded by the "where clause." Basically, within a Search Cursor, I put lines to this effect:

with arcpy.da.SearchCursor(fc, fields) as cursor: for row in cursor: # Only include records where APN exists: if row[0]: # row[0] is the APN # then do stuff

This works, but it would be less clunky to just be able to use a "where clause" in MakeTableView. I'd still be very interested to find out why it doesn't seem to work with the type of table I'm using.

Re: Where Clause is being ignored in MakeTableView

DougBrowning — Fri, 20 Oct 2023 18:12:40 GMT

Have you tried "NOT APN IS NULL" or may need "NOT (APN IS NULL)" but I think I remember what you had will not work.