Where Clause is being ignored in MakeTableView

[AllenDailey1]

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!

[ChrisUnderwood]

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.

[AllenDailey1]

Thank you very much for your suggestion!

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

[ChrisUnderwood]

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

[AllenDailey1]

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.

[AllenDailey1]

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.

[DougBrowning]

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.